34,838
社区成员




select id,class,marks from
(
select t.* , px = row_number() rank(partition class order by marks desc) from tb t
) m
where px <= 3
select t.* from tb t where marks in (select top 3 marks from tb where class = t.class order by marks desc)
if object_id('tb') is not null drop table tb
create table tb(id int,class int,marks int)
insert tb
select 1,1,100 union all
select 2,1,99 union all
select 3,1,96 union all
select 4,1,90 union all
select 5,2,97 union all
select 6,2,85 union all
select 7,2,93 union all
select 8,2,85 union all
select 9,3,90
select bb.* from (select *,row_number() over (partition by class order by marks) rank from tb) bb
where bb.rank<=3 order by bb.class,bb.rank
id class marks rank
----------- ----------- ----------- --------------------
4 1 90 1
3 1 96 2
2 1 99 3
6 2 85 1
8 2 85 2
7 2 93 3
9 3 90 1
(7 行受影响)
--SQL2005适用
if object_id('tb') is not null drop table tb
go
create table tb(id int,class int,marks int)
insert tb
select 1,1,100 union all
select 2,1,99 union all
select 3,1,96 union all
select 4,1,90 union all
select 5,2,97 union all
select 6,2,85 union all
select 7,2,93 union all
select 8,2,85 union all
select 9,3,90
--如果并列都算用RANK(),排除并列用DENSE_RANK()
select *
from (select *,RANK() OVER(PARTITION BY class ORDER BY marks desc) as [Order] FROM tb) a
where a.[Order] between 1 and 3
select *
from tb t
where (select count(1)+1 from tb where class=t.class and marks>t.marks)<=3
select t.* from tb t where id in(select top 3 id from tb where class=t.class order by marks desc) order by class,marks desc