select top 5 b.name as rs from class a,student b where a.id=b.classid group by b.name order by 2 desc
select class,id,name,score,rn from (select class,id,name,score, row_number() over(partition by class order by score desc) 'rn' from 学生总表) t where t.rn<=5
楼上的有问题 ,楼主要的是每个班的前5名;with cte as ( select rn=row_number() over(partition by classid order by score desc),* from tb ) select * from cte where rn<=5;
with t as( select *,row_number() over(partition by classid order by id desc) 'rn' from student ) select * from t where rn<6
--尝试下排名函数,呵呵create table Table_1 ( class varchar(10), id varchar(10), name varchar(10), score decimal(8,2) )insert into Table_1 values ('1','N1','A',87.89) insert into Table_1 values ('1','N2','B',77.89) insert into Table_1 values ('1','N3','C',88.67) insert into Table_1 values ('1','N4','D',66.89) insert into Table_1 values ('1','N5','E',88.65) insert into Table_1 values ('1','N6','E',99.65) insert into Table_1 values ('2','NA','Z',87.89) insert into Table_1 values ('2','NB','X',77.89) insert into Table_1 values ('2','NC','V',88.67) insert into Table_1 values ('2','ND','Y',66.89) insert into Table_1 values ('2','NE','U',88.65) insert into Table_1 values ('2','NF','T',99.65) SELECT * FROM Table_1select * from (SELECT RANK() OVER(PARTITION BY CLASS ORDER BY score desc) AS 排名,* FROM Table_1 )t where t.排名<=5
排名 class id name score -------------------- ---------- ---------- ---------- --------------------------------------- 1 1 N6 E 99.65 2 1 N3 C 88.67 3 1 N5 E 88.65 4 1 N1 A 87.89 5 1 N2 B 77.89 1 2 NF T 99.65 2 2 NC V 88.67 3 2 NE U 88.65 4 2 NA Z 87.89 5 2 NB X 77.89(10 行受影响)
where a.id=b.classid
group by b.name order by 2 desc
select class,id,name,score,rn
from
(select class,id,name,score,
row_number() over(partition by class order by score desc) 'rn'
from 学生总表) t where t.rn<=5
select rn=row_number() over(partition by classid order by score desc),* from tb
)
select * from cte where rn<=5;
select *,row_number() over(partition by classid order by id desc) 'rn' from student
)
select * from t where rn<6
(
class varchar(10), id varchar(10), name varchar(10), score decimal(8,2)
)insert into Table_1 values ('1','N1','A',87.89)
insert into Table_1 values ('1','N2','B',77.89)
insert into Table_1 values ('1','N3','C',88.67)
insert into Table_1 values ('1','N4','D',66.89)
insert into Table_1 values ('1','N5','E',88.65)
insert into Table_1 values ('1','N6','E',99.65)
insert into Table_1 values ('2','NA','Z',87.89)
insert into Table_1 values ('2','NB','X',77.89)
insert into Table_1 values ('2','NC','V',88.67)
insert into Table_1 values ('2','ND','Y',66.89)
insert into Table_1 values ('2','NE','U',88.65)
insert into Table_1 values ('2','NF','T',99.65)
SELECT * FROM Table_1select * from
(SELECT RANK() OVER(PARTITION BY CLASS ORDER BY score desc) AS 排名,* FROM Table_1 )t
where t.排名<=5
-------------------- ---------- ---------- ---------- ---------------------------------------
1 1 N6 E 99.65
2 1 N3 C 88.67
3 1 N5 E 88.65
4 1 N1 A 87.89
5 1 N2 B 77.89
1 2 NF T 99.65
2 2 NC V 88.67
3 2 NE U 88.65
4 2 NA Z 87.89
5 2 NB X 77.89(10 行受影响)