时间戳

首页 > 解决方案 > SQL聚合查询重复主键列

sql - SQL聚合查询重复主键列

问题描述

我正在查询两个数据库表，Invoice并且Payments. 我想获取特定发票的所有付款的总和，但是结果“重复”发票编号而不是汇总值。

这些表是

发票

invoiceID
dueDate
invoiceAmount

付款

paymentID
invoiceID
fine
amount

这是我的查询：

SELECT 
    Invoices.invoiceID, invoiceAmount, 
    COALESCE((SUM (fine + amount)), 0) AS 'Total'  
FROM 
    Payments, Invoices 
WHERE 
    Payments.invoiceID = Invoices.invoiceID
GROUP BY  
    Invoices.invoiceID, dueDate, datePaid, invoiceAmount
ORDER BY 
    Invoices.invoiceID

结果是这样的

invoiceID invoiceAmount Total
---------------------------------
   2        270000      170000
   2        270000      100000
  67        400000      150000
  67        400000      250000

我希望输出是

invoiceID    invoiceAmount  Total 
----------------------------------
   2           270000       270000
  67           400000       400000

标签： sql

解决方案

我怀疑您真的想要left join捕获没有付款的发票：

SELECT i.invoiceID, i.invoiceAmount, 
       SUM(COALESCE(fine, 0) + COALESCE(amount, 0)) as Total
FROM Invoices i LEFT JOIN
     Payments p  
     ON p.invoiceID = i.invoiceID
GROUP BY i.invoiceID, i.invoiceAmount
ORDER BY i.invoiceID;

请注意，COALESCE()用于每列。如果其中一个值是NULL，那么这可能符合您的意图。

推荐阅读