SQL需求：仅保留同一Code对应最高Level的数据并完成统计

需求说明 #

同一Code（用户）对应多个Level（取值为01、02、03、04、05或99），需先保留每个Code对应的最高Level记录，再执行统计逻辑。

原查询代码 #

select  
Level,
count(Code) as Atendee,
count(CASE WHEN CodPlace = 01 THEN (Atendee) ELSE null END ) as AtendeeCho,
count(CASE WHEN CodPlace = 02 THEN (Atendee) ELSE null END ) as AtendeeSB,
count(CASE WHEN CodPlace = 14 THEN (Atendee) ELSE null END ) as AtendeeIca 
from  #TempoPar  
group by Level 
order by Level

原查询结果 #

Level Atendee AtendeeCho AtendeeSB AtendeeIca

1      3       2           0        1

2      0       0           0        0

3      2       2           0        0

99     1       1           0        0

问题点 #

Level 1、3、99中的AtendeeCho对应同一用户，该用户应仅出现在Level 99中；若无Level99，则出现在Level3中，原查询未做去重导致统计重复。

数据示例 #

Code (user)  Level Career CodPlace 
12345         1     9      01
12345         3     15     01
12346         1     10     14
12347         1     10     01
12345         3     15     01
12347         99    15     01

期望筛选后的数据 #

Code (user)  Level Career CodPlace 
12345         3     15     01
12346         1     10     14
12347         99    15     01

期望最终统计结果 #

Level Atendee AtendeeCho AtendeeSB AtendeeIca

1      1       0           0        1

2      0       0           0        0

3      1       1           0        0

99     1       1           0        0

解决方案 #

使用窗口函数ROW_NUMBER()按用户分组，取每组内最高Level的记录，再基于该数据集执行统计：

WITH FilteredData AS (
    SELECT 
        Code, Level, Career, CodPlace,
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY CAST(Level AS INT) DESC) AS rn
    FROM #TempoPar
)
SELECT  
    Level,
    COUNT(Code) AS Atendee,
    COUNT(CASE WHEN CodPlace = 01 THEN 1 ELSE NULL END) AS AtendeeCho,
    COUNT(CASE WHEN CodPlace = 02 THEN 1 ELSE NULL END) AS AtendeeSB,
    COUNT(CASE WHEN CodPlace = 14 THEN 1 ELSE NULL END) AS AtendeeIca 
FROM FilteredData
WHERE rn = 1
GROUP BY Level 
ORDER BY Level;

逻辑说明 #

• PARTITION BY Code：按用户维度分组
• ORDER BY CAST(Level AS INT) DESC：将Level转为整数后降序排序，确保99为最高优先级，依次往下
• rn = 1：筛选每个用户的最高Level记录，避免重复统计
• 统计部分保留原逻辑，但基于去重后的唯一用户记录计算

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