MySQL技术问题：如何按年份筛选prince值最高的单个用户

现有查询语句 #

SELECT e.frist_name, SUM(e.prince), YEAR(e.hire_date) AS year FROM (
   SELECT * FROM employee WHERE termination IS NULL
) e
JOIN storage s ON e.id = s.eid GROUP BY e.id, e.hire_date;

现有查询结果 #

+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn        | 1450          | 2012 |
+--------------+---------------+------+
| Emma         | 1020          | 2013 |
+--------------+---------------+------+
| Gordon       | 340           | 2012 |
+--------------+---------------+------+
| Ave          | 600           | 2014 |
+--------------+---------------+------+

需求说明 #

需要从上述结果中筛选出每年仅保留prince总和最高的单个用户，最终目标结果如下：

+--------------+---------------+------+
| e.first_name | SUM(e.prince) | year |
+--------------+---------------+------+
| Johnn        | 1450          | 2012 |
+--------------+---------------+------+
| Emma         | 1020          | 2013 |
+--------------+---------------+------+
| Ave          | 600           | 2014 |
+--------------+---------------+------+

解决方案 #

方案1：使用窗口函数（MySQL 8.0及以上版本） #

利用窗口函数ROW_NUMBER()按年份分组，对prince总和降序排序后取每组第一条数据，写法简洁高效：

WITH yearly_totals AS (
    SELECT 
        e.first_name, 
        SUM(e.prince) AS total_prince, 
        YEAR(e.hire_date) AS year
    FROM employee e
    JOIN storage s ON e.id = s.eid
    WHERE e.termination IS NULL
    GROUP BY e.id, e.first_name, YEAR(e.hire_date)
),
ranked_totals AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY total_prince DESC) AS rn
    FROM yearly_totals
)
SELECT first_name, total_prince, year
FROM ranked_totals
WHERE rn = 1;

方案2：使用子查询（兼容MySQL 5.x版本） #

如果你的MySQL版本不支持窗口函数，可通过子查询先计算每年的最高prince总和，再关联筛选目标记录：

SELECT 
    e.first_name, 
    SUM(e.prince) AS total_prince, 
    YEAR(e.hire_date) AS year
FROM employee e
JOIN storage s ON e.id = s.eid
WHERE e.termination IS NULL
GROUP BY e.id, e.first_name, YEAR(e.hire_date)
HAVING SUM(e.prince) = (
    SELECT MAX(sub.total)
    FROM (
        SELECT SUM(e2.prince) AS total
        FROM employee e2
        JOIN storage s2 ON e2.id = s2.eid
        WHERE e2.termination IS NULL AND YEAR(e2.hire_date) = YEAR(e.hire_date)
        GROUP BY e2.id
    ) sub
);

补充说明 #

• 方案1中先通过CTE计算每个员工每年的prince总和，再给同一年份的记录按总和降序排名，最终取排名为1的记录；若同一年有多个用户总和并列最高，ROW_NUMBER()会随机保留一个，如需保留全部可改用RANK()。
• 方案2通过子查询获取对应年份的最高总和，再用HAVING筛选匹配的记录，若存在同年份多用户总和并列最高的情况，该方案会返回所有符合条件的用户。

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