带有sum函数的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]; 

- Ashenafi Semu

---

使用哪种数据库？请注明版本。 - OMG Ponies

为什么每个项目都需要信息，特别是当您需要余额时？付款表是否存储给定发票的每个项目的记录？（我不这样认为） - shahkalpesh

我尝试了这个查询，但它不起作用。它的问题是什么？语法错误？错误的结果集？请提供详细信息：样本数据、期望结果、错误消息和不良行为的具体细节。 - APC

"Q.Amount"是什么？你能发布你的表定义吗？ - Mark Byers

必须假设amount存在于PAYMENT表中 - 这就是为什么您的查询失败的原因，因为您有SUM(Q.amount)，但在Q派生表/子查询中没有amount列。 - OMG Ponies

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

- Mark Byers

---

抱歉回复晚了，但我的数据库是在Access中，我认为COALESCE函数不适用于Access。 - Ashenafi Semu

@user249641：我认为你需要使用NZ。记得在问题中始终标记数据库的类型。 - Mark Byers

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 用于假设发票可能没有付款记录。

- shahkalpesh

---

现在显示“无效的列名'InvoiceID'”，应该是[发票ID]，而且我认为你漏了一个GROUP BY？ - Mark Byers

@Mark：我认为我应该尝试使用一个示例来测试这个查询是否有效。我认为直接在SO的文本框中编写而不进行检查并不是正确的回答方式。 - shahkalpesh

@Jonathan：我已经使用COALESCE编辑了答案，以处理空值（即如果Payment表中没有行）。 - shahkalpesh

@shahkalpesh：实际上，我指的是他原始查询中的类似Invoice.[Payment Status]和Invoice.[LongDate]这样的字段。 - Mark Byers

@Mark：是的。但我不明白为什么OP要以这种方式存储记录？（即如果发票可能有多个项目） - shahkalpesh