如何从员工表与夜班记录表中筛选累计夜班次数最多的员工？

要解决这个问题，我们需要先将Hours表中分散在两个字段的员工ID合并为单列，统计每个员工的夜班次数，再关联Users表获取姓名，最终筛选出次数最多的员工。

解决思路 #

1. 拆分并合并夜班记录：用UNION ALL把NightShift1和NightShift2的员工ID合并成一个列表，确保每条夜班里的两个员工都能被单独计数。
2. 统计夜班次数：对合并后的员工ID分组，计算每个ID的出现次数。
3. 关联员工姓名：把统计结果和Users表关联，拿到对应的员工姓名。
4. 筛选最高次数：按次数降序排序，取排名第一的员工（如果有并列最多的情况，可调整逻辑返回所有并列员工）。

示例SQL代码 #

SELECT u.name, COUNT(*) AS total_night_shifts
FROM (
    SELECT NightShift1 AS user_id FROM Hours
    UNION ALL
    SELECT NightShift2 AS user_id FROM Hours
) AS shift_records
JOIN Users u ON shift_records.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_night_shifts DESC
LIMIT 1;

结果验证 #

根据提供的数据手动统计：

• Oliver（ID1）：5次
• Harry（ID2）：6次
• Jacob（ID3）：5次

执行上述SQL后会返回：

name  | total_night_shifts
------|---------------------
Harry | 6

如果需要返回所有并列次数最多的员工（比如存在多个员工次数相同且为最高），可以去掉LIMIT 1，改用窗口函数实现：

SELECT name, total_night_shifts
FROM (
    SELECT u.name, COUNT(*) AS total_night_shifts,
           RANK() OVER(ORDER BY COUNT(*) DESC) AS rnk
    FROM (
        SELECT NightShift1 AS user_id FROM Hours
        UNION ALL
        SELECT NightShift2 AS user_id FROM Hours
    ) AS shift_records
    JOIN Users u ON shift_records.user_id = u.id
    GROUP BY u.id, u.name
) AS ranked_shifts
WHERE rnk = 1;

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