如何统计每位员工最新通过的Action数量？SQL查询求助

问题分析 #

你的原查询未先筛选出每个员工的最新Action记录，而是直接按phase分组统计所有符合条件的记录，导致非最新的Action也被计入统计。要实现需求，需先定位每个员工最新日期对应的Action，再进行统计。

修正方案（两种常用写法） #

方法1：子查询获取每个员工的最新日期

先通过子查询锁定目标员工（cand_info.d_id=30 AND cand_info.p_id=22）各自的最新int_date，再关联int_scores表获取对应phase，最后统计数量：

SELECT 
    src.phase AS Phase,
    COUNT(src.phase) AS Count
FROM 
    int_scores src
JOIN (
    -- 获取每个目标员工的最新面试日期
    SELECT 
        c.c_id,
        MAX(s.int_date) AS latest_date
    FROM 
        cand_info c
    JOIN int_scores s ON c.c_id = s.c_id
    WHERE 
        c.d_id = 30 AND c.p_id = 22 AND s.status = 'passed'
    GROUP BY 
        c.c_id
) latest ON src.c_id = latest.c_id AND src.int_date = latest.latest_date
WHERE 
    src.status = 'passed'
GROUP BY 
    src.phase
ORDER BY 
    src.phase;

方法2：窗口函数标记最新记录

用ROW_NUMBER()窗口函数为每个员工的记录按int_date倒序排序，标记出最新的那条记录，再筛选标记为1的记录统计：

SELECT 
    Phase,
    COUNT(Phase) AS Count
FROM (
    SELECT 
        src.phase AS Phase,
        -- 按员工分组，日期倒序，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY src.c_id ORDER BY src.int_date DESC) AS rn
    FROM 
        int_scores src
    JOIN cand_info c ON src.c_id = c.c_id
    WHERE 
        c.d_id = 30 AND c.p_id = 22 AND src.status = 'passed'
) ranked
WHERE rn = 1 -- 仅保留最新记录
GROUP BY Phase
ORDER BY Phase;

结果验证 #

两种写法都会返回符合预期的结果：

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