SQL查询无LastMonth/CurrentMonth记录时返回重复行问题排查

问题根源 #

重复行的核心原因是连续FULL OUTER JOIN时的关联逻辑断裂：
当LastMonth返回空结果时，第三个JOIN（LastMonth l FULL OUTER JOIN ProjectCount pc）的关联条件l.Project_Name = pc.Project_Name因l无数据，无法匹配pc的记录。FULL JOIN会同时保留两边无匹配的行：

• 一行是ThisWeek+ThisMonth的原有数据，pc.ProCount被COALESCE填充为0；
• 另一行是ProjectCount的原有数据，w.thisWeekAvg、m.thisMonthAvg被COALESCE填充为0；
  最终导致同项目出现两条重复记录。

修复方案1：调整JOIN关联条件，避免逻辑断裂 #

修改每个FULL JOIN的关联条件，用COALESCE聚合前面所有表的Project_Name，确保后续JOIN能基于已存在的项目名称匹配，不会因中间表为空产生额外行：

WITH 
ThisWeek AS (SELECT  'xyz' as 'Project_Name', 0.19 as 'thisWeekAvg' ), 
ThisMonth AS ( SELECT  'xyz' as 'Project_Name', 0.09 as 'thisMonthAvg'), 
LastMonth AS( SELECT  'xyz' as 'Project_Name', 0 as 'lastMonthAvg'  where 1=2), 
CurrentDate AS( SELECT  'xyz' as 'Project_Name', 0 as 'CurrentDateAvg'  where 1=2), 
ProjectCount AS( SELECT  'xyz' as 'Project_Name', 5 AS 'ProCount' ) 
SELECT 
    COALESCE(w.Project_Name, m.Project_Name, l.Project_Name,c.Project_Name,pc.Project_Name) AS 'Project',
    COALESCE(pc.ProCount,0) 'ProCount', 
    COALESCE(c.CurrentDateAvg,0) 'CurrentDateAvg', 
    COALESCE(w.thisWeekAvg,0) 'thisWeekAvg', 
    COALESCE(m.thisMonthAvg,0) 'thisMonthAvg', 
    COALESCE(l.lastMonthAvg,0) 'lastMonthAvg' 
FROM 
    ThisWeek w 
    FULL OUTER JOIN ThisMonth m ON w.Project_Name = m.Project_Name
    FULL OUTER JOIN LastMonth l ON COALESCE(w.Project_Name, m.Project_Name) = l.Project_Name
    FULL OUTER JOIN ProjectCount pc ON COALESCE(w.Project_Name, m.Project_Name, l.Project_Name) = pc.Project_Name 
    FULL OUTER JOIN CurrentDate c ON COALESCE(w.Project_Name, m.Project_Name, l.Project_Name, pc.Project_Name) = c.Project_Name

修复方案2：构建项目主表，用LEFT JOIN关联子表（更易维护） #

先通过UNION收集所有存在的项目，生成唯一项目的主表，再用LEFT JOIN关联各个统计子表，从根源避免重复行：

WITH 
AllProjects AS (
    SELECT Project_Name FROM ThisWeek
    UNION
    SELECT Project_Name FROM ThisMonth
    UNION
    SELECT Project_Name FROM LastMonth
    UNION
    SELECT Project_Name FROM CurrentDate
    UNION
    SELECT Project_Name FROM ProjectCount
),
ThisWeek AS (SELECT  'xyz' as 'Project_Name', 0.19 as 'thisWeekAvg' ), 
ThisMonth AS ( SELECT  'xyz' as 'Project_Name', 0.09 as 'thisMonthAvg'), 
LastMonth AS( SELECT  'xyz' as 'Project_Name', 0 as 'lastMonthAvg'  where 1=2), 
CurrentDate AS( SELECT  'xyz' as 'Project_Name', 0 as 'CurrentDateAvg'  where 1=2), 
ProjectCount AS( SELECT  'xyz' as 'Project_Name', 5 AS 'ProCount' ) 
SELECT 
    ap.Project_Name AS Project,
    COALESCE(pc.ProCount, 0) AS ProCount,
    COALESCE(c.CurrentDateAvg, 0) AS CurrentDateAvg,
    COALESCE(w.thisWeekAvg, 0) AS thisWeekAvg,
    COALESCE(m.thisMonthAvg, 0) AS thisMonthAvg,
    COALESCE(l.lastMonthAvg, 0) AS lastMonthAvg
FROM AllProjects ap
LEFT JOIN ThisWeek w ON ap.Project_Name = w.Project_Name
LEFT JOIN ThisMonth m ON ap.Project_Name = m.Project_Name
LEFT JOIN LastMonth l ON ap.Project_Name = l.Project_Name
LEFT JOIN ProjectCount pc ON ap.Project_Name = pc.Project_Name
LEFT JOIN CurrentDate c ON ap.Project_Name = c.Project_Name

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