SQL Server左连接分组查询保留无关联明细主表记录的方法

业务背景 #

现有SQL Server订单业务两张表：tblMain 订单主表、tblDetail 订单明细表，二者通过 tblMain.id = tblDetail.OrderID 关联。

示例表结构 #

/* 创建并插入订单主表测试数据 */
CREATE TABLE tblMain (
    ID int IDENTITY(1,1) NOT NULL,
    DateOrder datetime NULL,
  CONSTRAINT PK_tblMain PRIMARY KEY
  (
      ID ASC
  )
)
GO

INSERT INTO tblMain (DateOrder) VALUES('2021-05-20T12:12:10');
INSERT INTO tblMain (DateOrder) VALUES('2021-05-21T09:13:13');
INSERT INTO tblMain (DateOrder) VALUES('2021-05-22T21:30:28');
GO

/* 创建并插入订单明细表测试数据 */
CREATE TABLE tblDetail (
    ID int IDENTITY(1,1) NOT NULL,
    OrderID int NULL,
    Gencod VARCHAR(255),
    Quantity float,
    Price float,
    IsActive bit DEFAULT 1, -- 明细行生效标识，false时不参与计算
  CONSTRAINT PK_tblDetail PRIMARY KEY
  (
      ID ASC
  )
)
GO

INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(1, '1234567890123', 8, 12.30);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(1, '5825867890321', 2, 2.88);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '7788997890333', 1, 1.77);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '9882254656215', 3, 5.66);
INSERT INTO tblDetail (OrderID, Gencod, Quantity, Price) VALUES(3, '9665464654654', 4, 10.64);
GO

问题描述 #

原有分组统计订单总金额的查询语句如下：

SELECT tblMain.id,SUM(tblDetail.Quantity*tblDetail.Price) AS TotalPrice
  FROM tblMain LEFT JOIN tblDetail ON tblMain.id=tblDetail.orderid
  WHERE (tblDetail.Quantity<>0) GROUP BY tblMain.id;
GO

执行后仅返回id为1和3的主表记录，即使使用了LEFT JOIN，无对应明细的id=2主表记录仍被过滤。需求为：保留tblDetail.Quantity<>0的过滤约束，同时返回所有主表记录，无明细的主表对应总金额可返回NULL。

根因说明 #

LEFT JOIN后如果将右表（明细表）的过滤条件放在WHERE子句中，会等效转为内连接：无匹配明细的主表对应的明细字段为NULL，NULL <> 0的判断结果为未知，会被WHERE逻辑过滤掉。

可行解决方案 #

方案1：过滤条件写在JOIN的ON子句中 #

将明细过滤逻辑放在LEFT JOIN的关联条件里，仅过滤匹配上的明细行，无明细的主表记录不会被剔除：

SELECT tblMain.id,SUM(tblDetail.Quantity*tblDetail.Price) AS TotalPrice
FROM tblMain 
LEFT JOIN tblDetail 
  ON tblMain.id=tblDetail.orderid
  AND tblDetail.Quantity <> 0 
  AND tblDetail.IsActive = 1
GROUP BY tblMain.id;
GO

方案2：WHERE子句补充NULL判断 #

过滤条件仍放在WHERE中，补充右表字段为NULL的判断，保留无明细的主表记录：

SELECT tblMain.id,SUM(tblDetail.Quantity*tblDetail.Price) AS TotalPrice
FROM tblMain 
LEFT JOIN tblDetail 
  ON tblMain.id=tblDetail.orderid
WHERE (tblDetail.Quantity <> 0 OR tblDetail.Quantity IS NULL)
  AND (tblDetail.IsActive = 1 OR tblDetail.IsActive IS NULL)
GROUP BY tblMain.id;
GO

性能参考 #

测试场景：主表1.5万条记录，明细表百万级

• 方案1执行耗时37秒
• 方案2执行耗时29秒，性能较方案1提升20%

内容的提问来源于stack exchange，提问作者jacouh