如何在SQL中基于特定年份Top值筛选主体全量数据

现有存储人员年度现金持有量的表，样例数据如下：

year   person        cash
0   2020   personone     29
1   2021   personone     40
2   2020   persontwo     17
3   2021   persontwo     13
4   2020   personthree   62
5   2021   personthree   55 

需要实现的逻辑：

• 以2021年的cash值为排序依据，筛选出现金持有量排名前2的人员
• 返回这些入选人员所有年份的完整记录，最终结果按2021年现金值倒序排列，同一人员的记录按年份升序排列

期望输出结果：

year   person        cash
0   2020   personthree   62
1   2021   personthree   55
2   2020   personone     29
3   2021   personone     40

通用兼容写法（支持所有SQL版本） #

逻辑拆分：先查出2021年现金Top2的人员名单，再关联原表取出这些人员的全量记录，最后按规则排序。

SELECT t1.year, t1.person, t1.cash
FROM 你的表名 t1
INNER JOIN (
    SELECT person
    FROM 你的表名
    WHERE year = 2021
    ORDER BY cash DESC
    LIMIT 2
) t2 ON t1.person = t2.person
ORDER BY
    (SELECT cash FROM 你的表名 t3 WHERE t3.person = t1.person AND t3.year = 2021) DESC,
    t1.year ASC;

窗口函数写法（支持MySQL8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库，性能更优） #

通过窗口函数直接按人员维度绑定2021年的现金值做排名，筛选排名前2的记录即可：

WITH person_with_rank AS (
    SELECT
        year,
        person,
        cash,
        RANK() OVER(
            ORDER BY MAX(CASE WHEN year = 2021 THEN cash END) OVER(PARTITION BY person) DESC
        ) AS rk
    FROM 你的表名
)
SELECT year, person, cash
FROM person_with_rank
WHERE rk <= 2
ORDER BY
    MAX(CASE WHEN year = 2021 THEN cash END) OVER(PARTITION BY person) DESC,
    year ASC;

*说明：如果2021年存在现金值并列第2的场景，RANK()会返回所有并列的人员；如果需要严格返回仅2个人员，可将RANK()替换为ROW_NUMBER()。

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