动态查找未付发票-解网

问：

如何从下表中动态查找未付发票：

发票表

InvoiceID,  Date        CustomerID,     Amount
1           06/01/2022      1           5000.00
2           08/03/2022      1           4000.00
3           08/25/2022      1           3000.00
4           09/05/2022      1           4500.00
5           09/25/2022      1           4500.00
6           010/10/2022     1           2000.00
7           11/20/2022      1           2500.00

付款表：-

PaymentID    Date       CustomerID      Amount
1           06/10/2022      1           3000.00     
2           06/25/2022      1           4000.00
3           07/15/2022      1           2000.00
4           09/10/2022      1           3000.00
5           10/22/2022      1           4000.00
6           10/24/2022      1           1500.00
7           10/28/2022      1           1000.00
8           11/14/2022      1            500.00

sql-服务器

SELECT I.CustomerID
     , I.AmountTotal-ISNULL(P.AmountTotal,0) as AmountDiff
  FROM
     ( SELECT CustomerID
            , SUM(Amount) AmountTotal
         FROM <invoices_table>
        GROUP
           BY CustomerID
     ) I
 LEFT
OUTER 
 JOIN
    ( SELECT CustomerID
           , SUM(Amount) AmountTotal
        FROM <payments_table>
       GROUP
          BY CustomerID
    ) P
   ON I.CustomerID = P.CustomerID
WHERE I.AmountTotal <= P.AmountTotal

上一个：MySQL中分层数据的节点计数

下一个：为什么我收到 HTTP 404 Not Found 以获取使用 Mircrosft Graph API 获取访问令牌

动态查找未付发票

Dynamically find unpaid invoices

评论