在多对多关系中，在两个字段上获取惟一的记录。

I'm trying to get all one to one relations in a many to many table.

我试着把所有的关系都放在很多很多的表格中。

In the example below the only record I want to return would be the last (3,3)

在下面的示例中，我希望返回的记录是最后一个(3,3)

PaymentID InvoiceID
1          1
1          2
2          2
3          3

The closest I have gotten is

离我最近的一次是

Select * from Table where PaymentID in (
select PaymentID from Table t
inner join (
    select InvoiceId from Table 
    group by InvoiceId
    having count(InvoiceId) = 1
) inv on t.InvoiceId = inv.InvoiceId
group by PaymentId
having count(PaymentId) = 1
)

This will return 1 and 3

这将返回1和3

Any Ideas greatly appreciated

任何想法大大赞赏

Thanks.

谢谢。

1 个解决方案

#1

SELECT  *
FROM    (
        SELECT  *,
                COUNT(*) OVER (PARTITION BY paymentId) AS pcnt,
                COUNT(*) OVER (PARTITION BY invoiceId) AS icnt
        FROM    mytable
        ) q
WHERE   pcnt = 1
        AND icnt = 1