如何从Emp表中获取重复频次最高的记录？

表数据与预期结果 #

Emp表原始数据 #

Id  JoiningDate Dept
AA  2019-11-16  X1
AA  2019-11-16  X1
AA  2019-11-18  X1
AA  2019-11-11  X1
AA  2019-11-11  X1
BB  2017-05-12  X1
BB  2017-05-12  X1
BB  2017-03-11  X1
BB  2017-11-30  YYY1
CC  2011-05-20  X1
CC  2010-05-27  X1

预期输出 #

Id  JoiningDate Dept
AA  2019-11-16  X1
BB  2017-05-12  X1
CC  2011-05-20  X1

筛选规则 #

• 优先保留重复次数最多的(Id, JoiningDate, Dept)组合；若同一Id下有多个组合重复次数相同，取日期最新的
• 若没有重复组合，直接取该Id下日期最新的记录

当前查询的不足 #

你现有的查询仅按Id, Dept分组取最大日期，没有考虑同一Id下不同日期组合的重复频次，无法满足第一条规则：

SELECT Id, Dept, MAX(JoiningDate)
FROM Emp where Source = 'X1'  -- 此处应为笔误，实际条件应为Dept = 'X1'
GROUP BY Id, Dept;

正确查询方案 #

我们需要先统计每个组合的重复次数，再按规则排序后为每个Id筛选出目标记录，以下是适配主流数据库的方案：

方案1：使用CTE（支持SQL Server、MySQL 8+、PostgreSQL等） #

WITH RankedData AS (
    SELECT 
        Id,
        JoiningDate,
        Dept,
        COUNT(*) AS repeat_count,
        -- 按重复次数降序、日期降序排序，为每个Id分配排名
        ROW_NUMBER() OVER (
            PARTITION BY Id 
            ORDER BY COUNT(*) DESC, JoiningDate DESC
        ) AS row_rank
    FROM Emp
    WHERE Dept = 'X1'
    GROUP BY Id, JoiningDate, Dept
)
SELECT Id, JoiningDate, Dept
FROM RankedData
WHERE row_rank = 1;

方案2：兼容低版本MySQL（无CTE支持） #

SELECT Id, JoiningDate, Dept
FROM (
    SELECT 
        Id,
        JoiningDate,
        Dept,
        COUNT(*) AS repeat_count,
        ROW_NUMBER() OVER (
            PARTITION BY Id 
            ORDER BY COUNT(*) DESC, JoiningDate DESC
        ) AS row_rank
    FROM Emp
    WHERE Dept = 'X1'
    GROUP BY Id, JoiningDate, Dept
) AS TempTable
WHERE row_rank = 1;

逻辑说明 #

1. 先对(Id, JoiningDate, Dept)分组，统计每组的重复次数repeat_count
2. 用ROW_NUMBER()按Id分区，先按repeat_count从高到低排序，次数相同则按JoiningDate从新到旧排序
3. 取每个分区中排名第1的记录，就是符合规则的目标记录

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