如何关联两个SQL查询且不破坏各自统计计数（一对多关联场景）

问题原因 #

你之前关联后统计计数错误，是因为没有先分别完成两个表的聚合计算再关联，直接关联原始表的话，一对多的关联关系会导致WO_RUN表的记录被重复计数，最终统计结果偏大。

实现方案 #

先通过CTE（公共表表达式）分别执行两个聚合查询得到各自的统计结果，再基于PartNumber和WO_PARTNUMBER的对应关系做关联，如需匹配你给出的折叠显示效果，可通过窗口函数做行号判断实现。
完整SQL如下：

WITH first_agg AS (
  SELECT 
    COUNT(xx.PartNumber) AS Total,
    xx.PartNumber,
    xx.PartDescription
  FROM WO_RUN AS xx 
  WHERE xx.QA_STATUS = 'A'
    AND xx.AddedDate > '12/31/2020'
  GROUP BY xx.doc_no, xx.PartNumber, xx.PartDescription
),
second_agg AS (
  SELECT 
    COUNT(yy.partnumber) AS Total_Parts,
    yy.WO_PARTNUMBER,
    yy.partnumber AS Second_PartNumber,
    yy.descriptn AS Partdescriptn
  FROM VIEW_PARTSTRACING_WO AS yy
  WHERE yy.QA_STATUS = 'A'
    AND yy.added_dte > '12/31/2020'
  GROUP BY yy.USER_DOC, yy.WO_PARTNUMBER, yy.partnumber, yy.descriptn
)
SELECT
  -- 仅每组第一行显示第一个查询的统计结果，其余行置空，匹配期望输出格式
  CASE WHEN ROW_NUMBER() OVER(PARTITION BY f.PartNumber ORDER BY s.Total_Parts DESC) = 1 THEN f.Total ELSE NULL END AS Total,
  CASE WHEN ROW_NUMBER() OVER(PARTITION BY f.PartNumber ORDER BY s.Total_Parts DESC) = 1 THEN f.PartNumber ELSE NULL END AS PartNumber,
  CASE WHEN ROW_NUMBER() OVER(PARTITION BY f.PartNumber ORDER BY s.Total_Parts DESC) = 1 THEN f.PartDescription ELSE NULL END AS PartDescription,
  s.Total_Parts,
  s.Second_PartNumber AS PartNumber,
  s.Partdescriptn
FROM first_agg f
LEFT JOIN second_agg s ON f.PartNumber = s.WO_PARTNUMBER
ORDER BY f.PartNumber, s.Total_Parts DESC;

注意事项 #

• 你原查询中的DISTINCT属于冗余写法，GROUP BY后的结果天然去重，删除后不影响结果还能提升执行效率。
• 如果不需要在SQL层面实现折叠效果，直接删除三个CASE WHEN判断，直接取f.Total、f.PartNumber、f.PartDescription即可，重复值可在报表工具、Excel中通过合并单元格处理。
• 示例中使用LEFT JOIN保留第一个查询中无匹配子件的记录，若不需要可替换为INNER JOIN。

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