SQL左连接保留左表全记录并聚合另一表数据时成员计数不准如何解决

问题根因 #

1. 关联条件字段名拼写错误：CTE1中定义的成员ID字段为MemID，LEFT JOIN时错误写为a.member_id，会导致无理赔的成员关联逻辑异常，无法正确被统计。
2. 时间字段拼写不统一：CTE1中时间字段为YearMonth，关联、查询时写为a.Yearmonth，大小写敏感的数据库会识别为不同字段，导致无理赔记录的成员时间字段取值为空，被排除在目标分组外。
3. 直接关联未聚合的理赔表：若同一成员同一月有多条理赔记录，会导致左表成员记录被重复拆分，虽然count(distinct)可去重，但存在统计误差风险。

修正后SQL #

WITH CTE1 AS (
    SELECT YearMonth, MemID, Flag
    FROM month a
    GROUP BY 1,2,3 -- 如月表本身无重复的月-成员记录，可删除该GROUP BY语句
),
-- 提前聚合理赔表，按成员+月份汇总理赔金额，避免左表数据膨胀
CTE_CLAIM AS (
    SELECT MemID, YearMonth, SUM(ClaimAmount) AS total_paid
    FROM table1
    GROUP BY MemID, YearMonth
)
SELECT 
    a.YearMonth, 
    a.Flag,
    COUNT(DISTINCT a.MemID) AS "MMs", 
    SUM(b.total_paid) AS "Paid"
FROM CTE1 a
LEFT JOIN CTE_CLAIM b 
    ON a.MemID = b.MemID -- 修正成员ID字段名
    AND a.YearMonth = b.YearMonth -- 统一时间字段拼写
GROUP BY 1,2
ORDER BY 1,2

效果验证 #

针对你提供的测试数据，修正后会正确统计Flag=N分组的2名成员：

• 有理赔记录的成员2正常累加2元理赔金额
• 无理赔记录的成员3会被计入成员计数，其对应理赔金额为NULL，求和时自动忽略，不影响最终理赔总额统计

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