如何对字符串使用GROUP BY与MAX？分组获取出现最多的Team_ID

需要按Department_ID和Employee_ID分组，返回每组中出现次数最多的team_id。

原始数据：

预期输出：

用户原执行SQL：

SELECT [Department_ID]
      ,[Employee_ID]
      , max(team_id)
FROM [AdventureWorksLT2019].[dbo].[table]
GROUP BY 
       [Department_ID]
      ,[Employee_ID]

该语句仅返回每组中team_id的最大值，而非出现次数最多的team_id，因此实际结果不符合预期：

可以通过统计分组频次+窗口函数排序的方式实现需求，SQL语句如下：

WITH TeamOccurrences AS (
    SELECT 
        Department_ID,
        Employee_ID,
        team_id,
        COUNT(*) AS count,
        -- 按部门+员工分组，按出现次数降序排序，次数相同时按team_id升序确保结果唯一
        ROW_NUMBER() OVER (PARTITION BY Department_ID, Employee_ID ORDER BY COUNT(*) DESC, team_id) AS rank_num
    FROM [AdventureWorksLT2019].[dbo].[table]
    GROUP BY Department_ID, Employee_ID, team_id
)
SELECT Department_ID, Employee_ID, team_id
FROM TeamOccurrences
WHERE rank_num = 1;

逻辑说明 #

1. 先通过CTETeamOccurrences统计每个(Department_ID, Employee_ID, team_id)组合的出现次数
2. 使用ROW_NUMBER()窗口函数，在每个(Department_ID, Employee_ID)分组内，按出现次数从高到低排序；若多个team_id出现次数相同，按team_id升序排列保证结果唯一
3. 最后筛选出排名为1的记录，即为每组出现次数最多的team_id

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