sql-server - 获取多对多关系中两个字段的唯一记录

Question

我试图在多对多表中获得所有一对一的关系。

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

PaymentID InvoiceID
1          1
1          2
2          2
3          3

我得到的最接近的是

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
)

这将返回 1 和 3

任何想法都非常感谢

谢谢。

Answer 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