sqlserver 如何求出分组后每组的第一行

答案:2 悬赏:80 手机版

解决时间 2021-01-29 03:24

提问者网友：美人性情
2021-01-28 15:19

最佳答案

五星知识达人网友：英雄的欲望
2021-01-28 16:58

SQL2005写法。
例如求出每个部门最高薪酬的人员信息。跟你每组第一行的要求很类似吧！

Declare @Employee table (ID int ,Dept int ,Salary decimal(10,2))
insert into @Employee values(1,10,5500.00)
insert into @Employee values(2,10,4500.00)
insert into @Employee values(3,20,1900.00)
insert into @Employee values(4,20,4800.00)
insert into @Employee values(5,40,6500.00)
insert into @Employee values(6,40,14500.00)
insert into @Employee values(7,40,44500.00)
insert into @Employee values(8,50,6500.00)
insert into @Employee values(9,50,7500.00)
--SELECt * FROM @Employee
SELECt
ID,Dept,Salary
FROM
(
SELECt
ROW_NUMBER() OVER (partition by Dept ORDER BY Salary desc) as Rank,
ID,Dept,Salary
FROM @Employee
) M
WHERe Rank=1
-----
执行结果：
---------
1 10 5500.00
4 20 4800.00
7 40 44500.00
9 50 7500.00

全部回答

1楼网友：封刀令
2021-01-28 17:35

select * from 表名 a where (select count(1) from 表名 where scorename=a.scorename and score>=a.score)<=2 order by scorename,score desc

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息