如何在MySQL中按员工月度筛选最高薪酬的4天记录

需求规则：

• 若员工月度工作日≥4天，仅保留该员工当月薪酬最高的4天记录
• 若员工月度工作日≤4天，则保留当月全部工作日记录

原始数据 #

筛选特定月份的期望结果 #

5月记录 #

1月记录 #

解决方案 #

可以通过窗口函数结合分组统计实现需求，具体SQL语句如下：

WITH emp_monthly_days AS (
    -- 统计每个员工每月的工作日数量
    SELECT 
        员工,
        DATE_FORMAT(STR_TO_DATE(日期, '%y-%m-%d'), '%Y-%m') AS 年月,
        COUNT(DISTINCT 日期) AS 工作日数
    FROM 薪资表
    GROUP BY 员工, DATE_FORMAT(STR_TO_DATE(日期, '%y-%m-%d'), '%Y-%m')
),
emp_daily_ranked AS (
    -- 对每个员工每月的日期按薪酬降序排名
    SELECT 
        日期,
        员工,
        薪酬,
        DATE_FORMAT(STR_TO_DATE(日期, '%y-%m-%d'), '%Y-%m') AS 年月,
        ROW_NUMBER() OVER(PARTITION BY 员工, DATE_FORMAT(STR_TO_DATE(日期, '%y-%m-%d'), '%Y-%m') ORDER BY 薪酬 DESC) AS 薪资排名
    FROM 薪资表
)
SELECT 
    d.日期,
    d.员工,
    d.薪酬
FROM emp_daily_ranked d
JOIN emp_monthly_days m 
    ON d.员工 = m.员工 AND d.年月 = m.年月
-- 核心筛选逻辑：工作日数≤4则全部保留，否则保留前4条高薪酬记录
WHERE (m.工作日数 <= 4) OR (d.薪资排名 <= 4)
-- 可选：添加特定月份筛选，比如筛选1月
-- AND d.年月 = '2023-01'
ORDER BY d.员工, d.日期;

逻辑说明 #

1. emp_monthly_days：先统计每个员工每月的独特工作日数量，用于判断后续筛选规则。
2. emp_daily_ranked：对每个员工每月的记录按薪酬从高到低排名，若需保留同薪酬的所有日期，可将ROW_NUMBER()替换为DENSE_RANK()。
3. 最终查询：关联两个统计结果，根据工作日数量触发不同筛选规则，同时可按需添加特定月份的过滤条件。

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