mysql按某字段值排序并取前n行

原始数据：

code	name	year	amount
1	a	2019	100
1	a	2020	200
1	a	2021	190
1	a	2022	180
2	b	2018	190
2	b	2019	200
2	b	2020	230

输出数据：

code	name	year	amount
1	a	2021	190
1	a	2022	180
2	b	2019	200
2	b	2020	230

SQL:

select code, name,year, amount from (
         SELECT code, name,year, amount,
            @date_rank := IF(@current_code = code, @date_rank + 1, 1) AS date_rank,
            @current_code := code
         FROM table
         ORDER BY code,year desc) ranked_rows
       where  date_rank<=2

SQL解释

子查询会根据年份倒序排序，给每行记录一个rank，父查询把date_rank<=2的筛选出来就行了。

SELECT code, name,year, amount,
            @date_rank := IF(@current_code = code, @date_rank + 1, 1) AS date_rank,
            @current_code := code
         FROM table
         ORDER BY code,year desc

子查询输出结果：

code	name	year	amount	date_rank	@current_code := code
1	a	2022	180	1	1
1	a	2021	190	2	1
1	a	2020	200	3	1
1	a	2019	100	4	1
2	b	2020	230	1	2
2	b	2019	200	2	2
2	b	2018	190	3	2