时间戳

首页 > 解决方案 > LEFT JOIN 返回重复的 SUM 值

sql-server - LEFT JOIN 返回重复的 SUM 值

问题描述

我有以下表格：

create table Invoices
(
  InvoiceID int,
  InvoiceNumber int,
  InvoiceDate date,
  SupplierName varchar(250),
  SupplierCode varchar(20),
  InvoiceValue decimal(18,2)  
);

insert into Invoices (InvoiceID, InvoiceNumber, InvoiceDate, SupplierName, SupplierCode, InvoiceValue) values 
(1,700,'2021-01-01','ACME','A01',978.32),
(2,701,'2021-01-02','MACROD','A02',772.81),
(3,702,'2021-01-03','CODECO','A03',938.20),
(4,703,'2021-01-04','ACME','A03',892.18),
(5,704,'2021-01-05','CODECO','A03',791.41),
(6,705,'2021-01-06','DRONIX','A04',469.03);

create table Payments
(
  InvoiceID int,
  PaymentDate date,  
  PaymentValue decimal(18,2)   
);

insert into Payments (InvoiceID, PaymentDate, PaymentValue) values 
(1, '2021-01-11', 500.00),
(1, '2021-01-12', 50.00),
(1, '2021-02-13', 100.00),
(3, '2021-02-14', 10.00),
(4, '2021-03-15', 200.00),
(3, '2021-03-16', 300.00),
(5, '2021-04-17', 75.00),
(1, '2021-04-18', 30.00);

这是我正在使用的查询：

SELECT
      a.SupplierName, 
      a.SupplierCode, 
      SUM(a.TotalInvoiceValue), 
      ISNULL(SUM(b.PaidAmount), 0), 
      SUM(a.TotalInvoiceValue) - ISNULL(SUM(b.PaidAmount), 0)
FROM (
    SELECT
      InvoiceID,
      SupplierName,
      SupplierCode,
      SUM(InvoiceValue) AS TotalInvoiceValue
    FROM Invoices  
    WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
    GROUP BY
      InvoiceID,
      SupplierName,
      SupplierCode
) a 
LEFT JOIN (
    SELECT
      InvoiceID,
      ISNULL(SUM(PaymentValue),0) AS PaidAmount
    FROM Payments
    GROUP BY InvoiceID
) b 
    ON a.InvoiceID=b.InvoiceID 
GROUP BY
  a.InvoiceID,
  a.SupplierName,
  a.SupplierCode
ORDER BY
  a.SupplierName

上面的查询为 Payments 表中的相同 SupplierName 返回多行。

我正在使用 Microsoft SQL Server 2005。

在此处查看 SQL 小提琴

标签： sql-servergroup-bysql-server-2005sumleft-join

解决方案

因为您按 (a.InvoiceID、a.SupplierName、a.SupplierCode) 分组，所以发票 ID、供应商名称和供应商代码的每个组合都有不同的记录。尝试按您的选择语句中的非聚合字段分组以返回不同的汇总行（在这种情况下，不要按发票 ID 分组，因为它不在您的选择语句中）。

在这种情况下，您选择 SupplierName 和 SupplierCode。这将复制每个供应商代码的供应商名称。如果您想要 SupplierName 的不同摘要记录，请不要选择供应商代码。

使用供应商代码

SELECT a.SupplierName, 
  a.SupplierCode, 
  SUM(a.TotalInvoiceValue), 
  ISNULL(SUM(b.PaidAmount),0), 
  SUM(a.TotalInvoiceValue)-ISNULL(SUM(b.PaidAmount),0)
FROM 
  (SELECT InvoiceID, SupplierName, SupplierCode, SUM(InvoiceValue) AS TotalInvoiceValue FROM Invoices  
  WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29' 
  GROUP BY InvoiceID, SupplierName, SupplierCode) a 
LEFT JOIN 
  (SELECT InvoiceID, ISNULL(SUM(PaymentValue),0) AS PaidAmount FROM Payments GROUP BY InvoiceID) b 
ON a.InvoiceID=b.InvoiceID 
GROUP BY a.SupplierName, a.SupplierCode
ORDER BY a.SupplierName

没有供应商代码

SELECT a.SupplierName, 
  SUM(a.TotalInvoiceValue), 
  ISNULL(SUM(b.PaidAmount),0), 
  SUM(a.TotalInvoiceValue)-ISNULL(SUM(b.PaidAmount),0)
FROM 
  (SELECT InvoiceID, SupplierName, SupplierCode, SUM(InvoiceValue) AS TotalInvoiceValue FROM Invoices  
  WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29' 
  GROUP BY InvoiceID, SupplierName, SupplierCode) a 
LEFT JOIN 
  (SELECT InvoiceID, ISNULL(SUM(PaymentValue),0) AS PaidAmount FROM Payments GROUP BY InvoiceID) b 
ON a.InvoiceID=b.InvoiceID 
GROUP BY a.SupplierName
ORDER BY a.SupplierName

推荐阅读