SQL UNION查询后合并相同SKU重复行 解决SUM字段为空问题

解决方案 #

你当前遇到的重复行问题，是因为UNION ALL会保留两个子查询的所有返回结果，相同SKU的两条记录分别只有一个SUM字段有值，另一个为NULL。要合并同SKU的行可以用以下两种方案：

方案1：改用条件聚合（更推荐，性能远优于UNION写法） #

无需拆分两次查询，直接在单次查询中用CASE语句区分时间范围求和，只需扫描一次订单表即可得到结果：

DECLARE @dt1 AS DATETIME 
SET @dt1 = DATEADD(day, 0, '2021/5/1')
DECLARE @dt2 AS DATETIME 
SET @dt2 = DATEADD(day, -30, getDate())

SELECT
  OD.[SKU] AS SKU,
  OD.[ProductName] AS Name,
  -- 统计2021年5月1日之后的总下单量
  SUM(CASE WHEN O.OrderDate > @dt1 THEN OD.[Qty] ELSE 0 END) AS #OrderedAllTime,
  -- 统计近30天的下单量
  SUM(CASE WHEN O.OrderDate > @dt2 THEN OD.[Qty] ELSE 0 END) AS #OrderedInLast30Days,
  (W.Onhand - W.Committed) as Available 
FROM ORD_OrderDetail AS OD
LEFT JOIN INV_OptionItemsKeys AS IK ON OD.SkuID = IK.SubItemID
INNER JOIN ORD_order AS O ON OD.ordernumber = O.recordnumber
JOIN INV_WarehouseItems W on OD.SkuID = W.ItemID 
WHERE O.Invoicedate IS NOT NULL
  AND O.void <> 1 
  -- 先过滤出所有符合时间要求的订单，减少扫描数据量
  AND O.OrderDate > @dt1 
GROUP BY OD.[SKU], OD.[ProductName], IK.[key], W.ItemID, W.OnHand, W.Committed

方案2：基于现有UNION代码外层加聚合合并行 #

如果要保留你原有UNION的写法，在外层套一层分组聚合，利用聚合函数忽略NULL值的特性合并行即可：

DECLARE @dt1 AS DATETIME 
SET @dt1 = DATEADD(day, 0, '2021/5/1')
DECLARE @dt2 AS DATETIME 
SET @dt2 = DATEADD(day, -30, getDate())

SELECT 
  SKU,
  Name,
  MAX(#OrderedAllTime) AS #OrderedAllTime,
  MAX(#OrderedInLast30Days) AS #OrderedInLast30Days,
  Available
FROM (
  (SELECT
    OD.[SKU] AS SKU,
    OD.[ProductName] AS Name,
    SUM(OD.[Qty]) AS #OrderedAllTime,
    null AS #OrderedInLast30Days,
   (W.Onhand - W.Committed) as Available FROM ORD_OrderDetail AS OD
    LEFT JOIN INV_OptionItemsKeys AS IK ON OD.SkuID = IK.SubItemID
    INNER JOIN ORD_order AS O ON OD.ordernumber = O.recordnumber
    JOIN INV_WarehouseItems W on OD.SkuID = W.ItemID WHERE O.Invoicedate IS NOT NULL
    AND O.void <> 1 
    AND O.OrderDate > @dt1 GROUP BY OD.[SKU], OD.[ProductName], IK.[key], W.ItemID, W.OnHand, W.Committed)
    UNION ALL
    (SELECT
    OD.[SKU] AS SKU,
    OD.[ProductName] AS Name,
    null AS #OrderedAllTime,
    SUM(OD.[Qty]) AS #OrderedInLast30Days,
   (W.Onhand - W.Committed) as Available FROM ORD_OrderDetail AS OD
    LEFT JOIN INV_OptionItemsKeys AS IK ON OD.SkuID = IK.SubItemID
    INNER JOIN ORD_order AS O ON OD.ordernumber = O.recordnumber
    JOIN INV_WarehouseItems W on OD.SkuID = W.ItemID WHERE O.Invoicedate IS NOT NULL
    AND O.void <> 1 
    AND O.OrderDate > @dt2 GROUP BY OD.[SKU], OD.[ProductName], IK.[key], W.ItemID, W.OnHand, W.Committed)
) AS t
GROUP BY SKU, Name, Available

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