如何让SQL正确统计多字段中External Professional的出现次数？

问题出在你用OR把四个字段的判断合并成单个CASE语句，导致每条记录最多仅返回1，完全忽略了多字段匹配的情况。要实现每个匹配字段单独计数，需要把每个字段的判断拆成独立的CASE语句，将它们的结果相加后再求和。

修改后的SQL如下：

SELECT 
    rqia_name AS Inspection,
    SUM(
        (CASE WHEN rqia_inspectorconductingidname = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorconducting2name = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorsupporting1idname = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorsupporting2idname = 'External Professional' THEN 1 ELSE 0 END)
    ) AS ExtProfInvolved
FROM Filteredrqia_inspection
GROUP BY rqia_name
HAVING SUM(
        (CASE WHEN rqia_inspectorconductingidname = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorconducting2name = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorsupporting1idname = 'External Professional' THEN 1 ELSE 0 END) +
        (CASE WHEN rqia_inspectorsupporting2idname = 'External Professional' THEN 1 ELSE 0 END)
    ) > 0

逻辑说明 #

• 每个独立的CASE语句单独判断对应字段是否为External Professional，匹配返回1，不匹配返回0
• 四个CASE结果相加，得到单条记录中外部专业人员的出现次数（最多4次）
• SUM函数对所有记录的次数求和，得到每组检查名称下的总出现次数
• HAVING子句基于总次数过滤，仅保留有外部专业人员参与的分组

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