[SQL Server习题] 每组ID薪水前N名

取表中每组id的TOP 2 salary

#创建表
create table student(id int,salary float,name varchar(255));
insert into student values
(1,25,'zhang'),
(1,75,'shan'),
(2,38,'li'),
(2,58,'si'),
(3,56,'wu'),
(4,68,'wang'),
(4,78,'jia'),
(4,32,'yi'),
(2,26,'bing'),
(4,98,'ding'),
(3,86,'xiong');

id	salary	name
1	25	zhang
1	75	shan
2	38	li
2	58	si
3	56	wu
4	68	wang
4	78	jia
4	32	yi
2	26	bing
4	98	ding
3	86	xiong

解题思路

（1）row_number() over (partition by col_1 order by col_2 desc)按照col_1分组，其中按col_2 降序排列，并生成一个新的字段rowNum;
（2）选择rowNum < 3，即为前2名；
（3）order by id, salary desc，按照id升序，salary降序排列。

实现代码

select id, name, salary
from (select *, row_number() over (partition by id order by salary desc) as row_num from student) a
where a.row_num <3
order by a.id, a.salary desc;        #按照id升序，salary降序排列

结果

id	name	salary
1	shan	75
1	zhang	25
2	si	58
2	li	38
3	xiong	86
3	wu	56
4	ding	98
4	jia	78