带求和的 Sql 子查询

Question

我有两张表：发票和付款。发票有付款。我想编写一个查询来显示未付发票和发票剩余金额，这是通过总结发票付款并从发票金额中减去它来计算的。我尝试了这个查询，但它不起作用。请问我该怎么做。

SELECT Invoice.[Invoice Date], Invoice.Item, Invoice.Quantity, 
    Invoice.[Unit Price], 
    Invoice.[Payment Status], Invoice.[LongDate], 
    Invoice.Quantity*Invoice.[Unit Price] - Sum(Q.Amount) AS Remaining
FROM  
    (SELECT Invoice.[Invoice Id], [Payment ID]
       FROM Invoice 
       INNER JOIN Payment ON Invoice.[Invoice Id] = Payment.[Invoice Id]) AS Q
    INNER JOIN Invoice ON Q.[Invoice Id] = Invoice.[Invoice Id]
GROUP BY Invoice.[Invoice Id]; 

Answer 1

试试这个：

SELECT
    Invoice.[Invoice Id],
    Invoice.Quantity * Invoice.[Unit Price] - COALESCE(Amount, 0) AS Remaining
FROM Invoice
LEFT JOIN (
    SELECT [Invoice Id], SUM(Amount) AS Amount
    FROM Payment
    GROUP BY [Invoice Id]
) T1
ON Invoice.[Invoice Id] = T1.[Invoice Id]

当然，您还需要将其他列添加到选择中，但我认为它们与这个问题无关，所以为了清楚起见，我省略了它们。

这是我用来测试这个的一些测试数据：

CREATE TABLE Invoice ([Invoice Id] INT NOT NULL, Quantity INT NOT NULL, [Unit Price] INT NOT NULL);
INSERT INTO Invoice ([Invoice Id], Quantity, [Unit Price]) VALUES
(1, 10, 5),
(2, 20, 10),
(3, 1, 1);

CREATE TABLE Payment ([Invoice Id] INT NOT NULL, Amount INT NOT NULL);
INSERT INTO Payment ([Invoice Id], Amount) VALUES
(1, 10),
(2, 100),
(2, 15);

以及使用这些数据的结果：

Id  Remaining
1   40
2   85
3   1

Answer 2

SELECT Invoice.[Invoice ID], 
    Sum(Invoice.Quantity * Invoice.[Unit Price]) 
    - COALESCE(Sum(Payment.Amount), 0) AS Remaining
FROM
    Invoice LEFT JOIN Payment ON Invoice.[Invoice ID] = Payment.[Invoice ID]
GROUP BY Invoice.[Invoice ID]

编辑：我假设，您在结果中不需要项目相关信息。
使用LEFT JOIN 时假设发票可能没有付款记录。