SQL Server中基于其他列值统计出现次数的问题求助

你的SQL语句存在两个关键问题，导致无法得到正确结果：

1. 缺少分组逻辑：使用SUM聚合函数但未添加GROUP BY子句，SQL Server会将全表合并为单一分组计算，无法返回每行对应的统计值。
2. 统计逻辑错误：原语句里的SUM是基于当前行字段做判断，而实际需求是统计全表中指定ManLevel列等于当前行ID的总记录数，不是当前行自身的匹配情况。

以下提供两种实用的实现方式：

方法一：关联子查询（直观易读） #

针对每行的ID，通过子查询根据AssignedLevel+1的结果，统计对应ManLevel列的匹配次数：

SELECT
    [ID],
    [AssignedLevel],
    [ManLevel02ID],
    [ManLevel03ID],
    [ManLevel04ID],
    [ManLevel05ID],
    CASE
        WHEN AssignedLevel + 1 = 2 THEN (SELECT COUNT(*) FROM EmpTable WHERE ManLevel02ID = e.ID)
        WHEN AssignedLevel + 1 = 3 THEN (SELECT COUNT(*) FROM EmpTable WHERE ManLevel03ID = e.ID)
        WHEN AssignedLevel + 1 = 4 THEN (SELECT COUNT(*) FROM EmpTable WHERE ManLevel04ID = e.ID)
        WHEN AssignedLevel + 1 = 5 THEN (SELECT COUNT(*) FROM EmpTable WHERE ManLevel05ID = e.ID)
        ELSE 0
    END AS OrgCount
FROM EmpTable e

方法二：预统计关联（性能更优） #

先提前统计所有ManLevel列的ID出现次数，再关联到主表对应行，适合大数据量场景：

WITH LevelCounts AS (
    SELECT 'Level02' AS LevelType, ManLevel02ID AS LevelID, COUNT(*) AS CountNum
    FROM EmpTable
    GROUP BY ManLevel02ID
    UNION ALL
    SELECT 'Level03' AS LevelType, ManLevel03ID AS LevelID, COUNT(*) AS CountNum
    FROM EmpTable
    GROUP BY ManLevel03ID
    UNION ALL
    SELECT 'Level04' AS LevelType, ManLevel04ID AS LevelID, COUNT(*) AS CountNum
    FROM EmpTable
    GROUP BY ManLevel04ID
    UNION ALL
    SELECT 'Level05' AS LevelType, ManLevel05ID AS LevelID, COUNT(*) AS CountNum
    FROM EmpTable
    GROUP BY ManLevel05ID
)
SELECT
    e.[ID],
    e.[AssignedLevel],
    e.[ManLevel02ID],
    e.[ManLevel03ID],
    e.[ManLevel04ID],
    e.[ManLevel05ID],
    ISNULL(lc.CountNum, 0) AS OrgCount
FROM EmpTable e
LEFT JOIN LevelCounts lc
    ON lc.LevelID = e.ID
    AND lc.LevelType = 'Level0' + CAST(e.AssignedLevel + 1 AS VARCHAR(2))

• 方法一逻辑直接，适合小数据集，容易调试和修改。
• 方法二通过一次预统计减少重复全表扫描，在数据量较大时能显著提升查询性能。

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