基于tbl_plt与TblOther两表的MethID关联SUM聚合统计需求及LEFT JOIN统计异常问题

你踩了LEFT JOIN关联多值表的经典坑：TblOther里同一个PNum对应多条MethID记录，直接关联后，tbl_plt的单条记录会被重复匹配，导致像TotalDetected、TotalP这类不涉及MethID的统计项被多计数，结果自然不对。

要实现「仅统计tbl_plt中MethID存在于同PNum的TblOther中的记录」，同时保证非MethID统计项准确，这里给你两种靠谱的解决方案：

方案1：用EXISTS子查询过滤，避免记录重复 #

这种方式不会改变tbl_plt的原始记录行数，非MethID统计项能保持正确，同时给每个涉及MethID的统计项加个EXISTS判断，确保只统计在TblOther中存在对应PNum+MethID的记录：

SELECT 
    p.pnum,
    SUM(CASE WHEN p.NegativeScreen = 'Type99' THEN 1 ELSE 0 END) TotalDetected,
    SUM(IIF(p.IsPositive = 1, 1, 0)) TotalP,
    -- 统计MethID不在(4,8,10,25)且在TblOther有对应PNum+MethID的记录数
    SUM(CASE WHEN (p.MethId NOT IN (4, 8, 10, 25)) 
                AND EXISTS(SELECT 1 FROM TblOther o WHERE o.PNum = p.PNum AND o.MethID = p.MethID)
             THEN 1 ELSE 0 END) Total,
    -- 统计MethID为34/64且在TblOther有对应PNum+MethID的记录数
    SUM(CASE WHEN (p.MethID IN (34,64)) 
                AND EXISTS(SELECT 1 FROM TblOther o WHERE o.PNum = p.PNum AND o.MethID = p.MethID)
             THEN 1 ELSE 0 END) TotalVal1,
    -- 统计MethID为16/64且在TblOther有对应PNum+MethID的记录数
    SUM(CASE WHEN (p.MethID IN (16,64)) 
                AND EXISTS(SELECT 1 FROM TblOther o WHERE o.PNum = p.PNum AND o.MethID = p.MethID)
             THEN 1 ELSE 0 END) TotalVal2,
    -- 统计符合条件的MethID且在TblOther有对应PNum+MethID的记录数
    SUM(CASE WHEN (p.MethID IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31) 
                    OR (p.MethID = 1 AND p.TotalCount IS NOT NULL))
                AND EXISTS(SELECT 1 FROM TblOther o WHERE o.PNum = p.PNum AND o.MethID = p.MethID)
             THEN 1 ELSE 0 END) TotalMethOther
FROM tbl_plt p
GROUP BY p.PNum

方案2：先预处理TblOther的唯一值集合，再关联统计 #

如果TblOther里有重复的PNum+MethID记录，先去重，再关联，确保tbl_plt每条记录只匹配一次：

WITH UniqueOtherMeth AS (
    -- 先拿到TblOther中唯一的PNum+MethID组合
    SELECT DISTINCT PNum, MethID FROM TblOther
)
SELECT 
    p.pnum,
    SUM(CASE WHEN p.NegativeScreen = 'Type99' THEN 1 ELSE 0 END) TotalDetected,
    SUM(IIF(p.IsPositive = 1, 1, 0)) TotalP,
    SUM(CASE WHEN p.MethId NOT IN (4, 8, 10, 25) THEN 1 ELSE 0 END) Total,
    SUM(CASE WHEN p.MethID IN (34,64) THEN 1 ELSE 0 END) TotalVal1,
    SUM(CASE WHEN p.MethID IN (16,64) THEN 1 ELSE 0 END) TotalVal2,
    SUM(CASE WHEN (p.MethID IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31) 
                    OR (p.MethID = 1 AND p.TotalCount IS NOT NULL))
             THEN 1 ELSE 0 END) TotalMethOther
FROM tbl_plt p
-- 只关联在TblOther中有对应PNum+MethID的记录
INNER JOIN UniqueOtherMeth o ON o.PNum = p.PNum AND o.MethID = p.MethID
GROUP BY p.PNum

两种方案的选择： #

• 方案1会保留tbl_plt中所有的PNum（即使TblOther里没有对应记录），只是涉及MethID的统计项会记0；
• 方案2只保留在TblOther中有对应PNum+MethID的PNum。如果需要保留所有原始PNum，把INNER JOIN改成LEFT JOIN，然后给每个涉及MethID的统计项加AND o.MethID IS NOT NULL的判断就行，比如：

WITH UniqueOtherMeth AS (
    SELECT DISTINCT PNum, MethID FROM TblOther
)
SELECT 
    p.pnum,
    SUM(CASE WHEN p.NegativeScreen = 'Type99' THEN 1 ELSE 0 END) TotalDetected,
    SUM(IIF(p.IsPositive = 1, 1, 0)) TotalP,
    SUM(CASE WHEN (p.MethId NOT IN (4, 8, 10, 25)) AND o.MethID IS NOT NULL THEN 1 ELSE 0 END) Total,
    SUM(CASE WHEN (p.MethID IN (34,64)) AND o.MethID IS NOT NULL THEN 1 ELSE 0 END) TotalVal1,
    SUM(CASE WHEN (p.MethID IN (16,64)) AND o.MethID IS NOT NULL THEN 1 ELSE 0 END) TotalVal2,
    SUM(CASE WHEN (p.MethID IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31) 
                    OR (p.MethID = 1 AND p.TotalCount IS NOT NULL))
                AND o.MethID IS NOT NULL
             THEN 1 ELSE 0 END) TotalMethOther
FROM tbl_plt p
LEFT JOIN UniqueOtherMeth o ON o.PNum = p.PNum AND o.MethID = p.MethID
GROUP BY p.PNum

这样就能完美解决你之前的统计异常问题，所有统计项都符合需求啦。

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