如何修改SQL查询员工每日总收益的最小与最大值？

我明白你的问题了——你已经能算出每个员工每天的总收益，但用MAX()/MIN()的时候没拿到当天的极值，反而还是每个员工自己的数对吧？这是因为你没把最值的计算限定在每一天范围内。我给你两种方案，都是基于先汇总每日员工收益的思路，来精准定位每天的最高和最低收益员工：

方案一：使用CTE分步计算（清晰易懂） #

先汇总每日每个员工的总收益，再单独计算每天的最大/最小总收益，最后关联筛选出匹配的员工：

-- 第一步：计算每个员工每天的总收益
WITH daily_employee_earnings AS (
    SELECT 
        day,
        employeeid,
        SUM(earned) AS total_earned
    FROM Employees
    GROUP BY day, employeeid
),
-- 第二步：计算每一天的总收益极值
daily_earnings_extremes AS (
    SELECT 
        day,
        MAX(total_earned) AS max_daily_earned,
        MIN(total_earned) AS min_daily_earned
    FROM daily_employee_earnings
    GROUP BY day
)
-- 第三步：关联筛选出当天收益等于极值的员工
SELECT 
    dee.day,
    dee.employeeid,
    dee.total_earned,
    CASE 
        WHEN dee.total_earned = dexe.max_daily_earned THEN 'Max Earner'
        WHEN dee.total_earned = dexe.min_daily_earned THEN 'Min Earner'
    END AS earner_type
FROM daily_employee_earnings dee
JOIN daily_earnings_extremes dexe ON dee.day = dexe.day
WHERE dee.total_earned IN (dexe.max_daily_earned, dexe.min_daily_earned)
ORDER BY dee.day, earner_type;

方案二：使用窗口函数（更简洁高效） #

利用窗口函数直接在汇总时计算每个员工在当天的收益排名，然后筛选排名第一的（最大和最小）员工：

WITH daily_employee_earnings AS (
    SELECT 
        day,
        employeeid,
        SUM(earned) AS total_earned,
        -- 按天分组，按总收益降序排名（第一就是当天最高）
        RANK() OVER (PARTITION BY day ORDER BY SUM(earned) DESC) AS rank_max,
        -- 按天分组，按总收益升序排名（第一就是当天最低）
        RANK() OVER (PARTITION BY day ORDER BY SUM(earned) ASC) AS rank_min
    FROM Employees
    GROUP BY day, employeeid
)
SELECT 
    day,
    employeeid,
    total_earned,
    CASE 
        WHEN rank_max = 1 THEN 'Max Earner'
        WHEN rank_min = 1 THEN 'Min Earner'
    END AS earner_type
FROM daily_employee_earnings
WHERE rank_max = 1 OR rank_min = 1
ORDER BY day, earner_type;

关键说明 #

• 为什么你之前的方法不对？因为你可能直接在汇总后的结果里调用MAX(total_earned)，但没有用PARTITION BY day（窗口函数）或者按day分组计算极值（CTE方法），导致计算的是所有天数的全局最值，而非每一天的最值。
• 用RANK()而不是ROW_NUMBER()的原因：如果同一天有多个员工总收益相同且都是极值，RANK()会保留所有符合条件的员工，而ROW_NUMBER()会随机只取一个，更符合业务需求。

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