sql-server - 查找没有匹配信用的发票

Question

简化表如下所示：

BillID|ProductID|CustomerID|Price|TypeID
------+---------+----------+-----+-------
111111|Product1 |Customer1 |  100| I
111112|Product1 |Customer1 | -100| C
111113|Product1 |Customer1 |  100| I
111114|Product1 |Customer1 | -100| C
111115|Product1 |Customer1 |  100| I

我需要找到具有匹配信用（C）但不是没有匹配信用（最后一条记录）的“奇数”发票的发票（I） - 或者相反（没有相应信用的不匹配发票）。

到目前为止，我有这个：

SELECT Invoices.billid, Credits.billid
FROM
    (SELECT B1.billid
    FROM billing B1
    WHERE B1.typeid='I') Invoices
INNER JOIN
    (SELECT B2.billid
    FROM billing B2
    WHERE B2.typeid='C') Credits
ON Invoices.customerid = Credits.customerid
    AND Invoices.productid = Credits.productid
    AND Invoices.price = -(Credits.price)

但它显然不起作用，因为它返回的东西看起来像：

billid | billid2
-------+ -------
111111 | 111112
111113 | 111114
111115 | 111114

我想得到的是不匹配的发票清单；

billid |
-------+
111115 |

或者只有匹配的发票；

billid | billid2
-------+ -------
111111 | 111112
111113 | 111114

发票号码（BillID）当然不一定是连续的，它只是一个简化的视图。

任何帮助，将不胜感激。

标签： sql-servertsql

Answer 1

这应该有效。我通过在信用之前添加一些连续的发票来进行测试。下面的查询显示了所有具有匹配信用的发票，如果不存在匹配项，则查询的别名“bar”部分显示为 NULL。

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER(Partition By TypeID, CustomerID, ProductID, Price ORDER BY BillID ASC) AS rownumber,
    *
  FROM Billing
)  AS foo
LEFT JOIN 
  (SELECT
    ROW_NUMBER() OVER(Partition By TypeID, CustomerID, ProductID, Price ORDER BY BillID ASC) AS rownumber,
    *
  FROM Billing
) AS bar
on foo.CustomerID = bar.CustomerID and 
foo.ProductID = bar.ProductID and 
foo.rownumber = bar.rownumber and 
foo.Price = -1*bar.Price
where foo.Price > 1

这是我使用的更新数据：

以下是我的结果：