将ROW_NUMBER OVER PARTITION转换为基础SQL实现员工Top5最长工时查询

嘿，我明白你还没接触过PARTITION这类窗口函数特性，想用基础SQL来解决这个问题对吧？没问题，咱们一步步来拆解，完全用基础的分组和关联子查询就能搞定。

第一步：先处理单日多条记录的问题 #

因为每位员工单日可能有多条工时记录，所以首先得把同一个员工同一天的工时加总，得到每日总工时。这一步用基础的GROUP BY就能实现：

SELECT 
    employee, 
    day, 
    SUM(worked_hours) AS total_hours
FROM `Table`  -- 注意：Table是很多数据库的关键字，用反引号或双引号包裹避免报错
GROUP BY employee, day;

这个子查询会得到一个包含员工、日期、当日总工时的临时结果集，我们后面的操作都基于这个结果。

第二步：用关联子查询筛选每个员工的Top5工时天数 #

接下来要给每个员工的每日总工时做“排名”，选出最长的5天。不用窗口函数的话，我们可以通过关联子查询统计当前记录的排名：

情况1：包含并列的Top5（比如有6天都是最高工时，这6天都会被选中） #

如果希望只要工时属于前5个最高的档次，就都保留，用COUNT(DISTINCT)来统计不同的工时等级：

SELECT 
    dt1.employee, 
    dt1.day, 
    dt1.total_hours
FROM (
    -- 第一步的每日总工时子查询
    SELECT 
        employee, 
        day, 
        SUM(worked_hours) AS total_hours
    FROM `Table`
    GROUP BY employee, day
) dt1
WHERE (
    -- 统计同一个员工中，总工时大于等于当前记录的不同工时值的数量
    SELECT COUNT(DISTINCT dt2.total_hours)
    FROM (
        SELECT 
            employee, 
            day, 
            SUM(worked_hours) AS total_hours
        FROM `Table`
        GROUP BY employee, day
    ) dt2
    WHERE dt2.employee = dt1.employee 
      AND dt2.total_hours >= dt1.total_hours
) <= 5  -- 只保留排名前5的工时等级对应的天数
ORDER BY dt1.employee, dt1.total_hours DESC, dt1.day;

情况2：严格取前5条（即使有并列，只保留前5天） #

如果不管并列情况，只取每个员工工时最长的5条记录，就把COUNT(DISTINCT)改成普通的COUNT(*)，同时加上日期排序来处理并列的情况：

SELECT 
    dt1.employee, 
    dt1.day, 
    dt1.total_hours
FROM (
    SELECT 
        employee, 
        day, 
        SUM(worked_hours) AS total_hours
    FROM `Table`
    GROUP BY employee, day
) dt1
WHERE (
    -- 统计同一个员工中，总工时比当前记录高，或者工时相同但日期更早的记录数量
    SELECT COUNT(*)
    FROM (
        SELECT 
            employee, 
            day, 
            SUM(worked_hours) AS total_hours
        FROM `Table`
        GROUP BY employee, day
    ) dt2
    WHERE dt2.employee = dt1.employee 
      AND (dt2.total_hours > dt1.total_hours 
           OR (dt2.total_hours = dt1.total_hours AND dt2.day <= dt1.day))
) <= 5
ORDER BY dt1.employee, dt1.total_hours DESC, dt1.day;

简单解释逻辑 #

核心思路就是：对于每一条“员工-日期”的总工时记录，我们去统计同一个员工里，有多少条记录的工时比它高（或者同等工时下日期更早），如果这个数量≤5，就说明这条记录属于该员工的Top5工时天数。

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