如何同时使用JOIN和UNION ALL合并两张表得到无重复完整数据

解决方案 #

你现在的写法用UNION ALL拆分了销售和预测两条数据，自然会出现同一维度分两行、各缺一个字段的情况，不需要组合UNION和JOIN，直接按三个共同关联维度（日期、Chain、Item）做全外连接就可以实现需求，SQL写法如下：

SELECT 
  COALESCE(s.DMDPostDate, f.STARTDATE) AS Date,
  COALESCE(s.LOC, f.U_CHAINNAME) AS Chain,
  COALESCE(s.DMDUNIT, f.DMDUNIT) AS Item,
  ISNULL(s.HistoryQuantity, 0) AS Sales,
  ISNULL(f.FORECAST_QTY, 0) AS Forecast
FROM SCPOMGR.HistByChainAndEvent_VW s
FULL OUTER JOIN SCPOMGR.DFUTOSKUFCSTWIDE_VW_CHAIN f
  ON s.LOC = f.U_CHAINNAME
  AND s.DMDPostDate = f.STARTDATE
  AND s.DMDUNIT = f.DMDUNIT
WHERE 
  COALESCE(s.DMDPostDate, f.STARTDATE) BETWEEN '2021-01-01' AND '2021-01-03'
  AND (ISNULL(s.HistoryQuantity, 0) > 0 OR ISNULL(f.FORECAST_QTY, 0) > 0)
GROUP BY 
  COALESCE(s.DMDPostDate, f.STARTDATE),
  COALESCE(s.LOC, f.U_CHAINNAME),
  COALESCE(s.DMDUNIT, f.DMDUNIT),
  ISNULL(s.HistoryQuantity, 0),
  ISNULL(f.FORECAST_QTY, 0)

如果你的数据库不支持FULL OUTER JOIN，可以用UNION先提取所有维度的唯一值，再分别关联销售和预测表：

WITH all_dim AS (
  SELECT DMDPostDate AS Date, LOC AS Chain, DMDUNIT AS Item FROM SCPOMGR.HistByChainAndEvent_VW WHERE DMDPostDate BETWEEN '2021-01-01' AND '2021-01-03' AND HistoryQuantity >0
  UNION
  SELECT STARTDATE AS Date, U_CHAINNAME AS Chain, DMDUNIT AS Item FROM SCPOMGR.DFUTOSKUFCSTWIDE_VW_CHAIN WHERE STARTDATE BETWEEN '2021-01-01' AND '2021-01-03' AND FORECAST_QTY>0
)
SELECT 
  d.Date,
  d.Chain,
  d.Item,
  ISNULL(s.HistoryQuantity, 0) AS Sales,
  ISNULL(f.FORECAST_QTY, 0) AS Forecast
FROM all_dim d
LEFT JOIN SCPOMGR.HistByChainAndEvent_VW s
  ON d.Date = s.DMDPostDate AND d.Chain = s.LOC AND d.Item = s.DMDUNIT
LEFT JOIN SCPOMGR.DFUTOSKUFCSTWIDE_VW_CHAIN f
  ON d.Date = f.STARTDATE AND d.Chain = f.U_CHAINNAME AND d.Item = f.DMDUNIT

你之前修改后出现重复行的问题，是因为关联的时候只加了Chain的条件，漏了日期和Item的关联，导致出现笛卡尔积，加上这两个关联条件就可以正常返回结果。

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