select a.id,a.classid,a.name,a.results from T_info a join T_info b on a.id= b.id group by a.id,a.classid,a.name,a.results having count(case when a.results <= b.results then 1 else null end) < = 20 --可动态修改 order by a.id,a.classid,a.name,a.results
create table T_info(id int,classid int,[name] varchar(20),results int) insert T_info values(1,1,'aa',60) insert T_info values(2,1,'aa',70) insert T_info values(3,1,'aa',80) insert T_info values(4,1,'aa',90) insert T_info values(5,1,'aa',100) insert T_info values(6,2,'bb',85) insert T_info values(7,2,'bb',67) insert T_info values(8,2,'bb',94) insert T_info values(9,2,'bb',63) insert T_info values(10,2,'bb',87) goselect a.id,a.classid,a.name,a.results from T_info a join T_info b on a.classid= b.classid group by a.id,a.classid,a.name,a.results having count(case when a.results <= b.results then 1 else null end) < = 3 --可动态修改 order by a.id,a.results desc drop table T_info/*id classid name results ----------- ----------- -------------------- ----------- 3 1 aa 80 4 1 aa 90 5 1 aa 100 6 2 bb 85 8 2 bb 94 10 2 bb 87(所影响的行数为 6 行)*/
只依次取前20select * from ( select id,classid,results,row_number() over(partition by classid order by results) ranks from T_info)a where ranks<=20 如果重复的也占名次,如第1和第2 人并列重复,则只有第一名和第三名,不设第二名,则改用Rankselect * from ( select id,classid,results,rank() over(partition by classid order by results) ranks from T_info)a where ranks<=20如果重复的不占名次,如第1和第2 人并列重复,则有两第一名,然后是第二名,则改用dense_Rankselect * from ( select id,classid,results,dense_rank() over(partition by classid order by results) ranks from T_info)a where ranks<=20
表T_info(id,classid,name,results) (id,班级ID,名字,成绩) 选出每个班级成绩前20名 ---------------------------------------- select * from T_info a where id in (select top 20 id from T_info where classid=a.classid order by name desc)
from T_info a
join T_info b on a.id= b.id
group by a.id,a.classid,a.name,a.results
having count(case when a.results <= b.results then 1 else null end) < = 20 --可动态修改
order by a.id,a.classid,a.name,a.results
insert T_info values(1,1,'aa',60)
insert T_info values(2,1,'aa',70)
insert T_info values(3,1,'aa',80)
insert T_info values(4,1,'aa',90)
insert T_info values(5,1,'aa',100)
insert T_info values(6,2,'bb',85)
insert T_info values(7,2,'bb',67)
insert T_info values(8,2,'bb',94)
insert T_info values(9,2,'bb',63)
insert T_info values(10,2,'bb',87)
goselect a.id,a.classid,a.name,a.results
from T_info a
join T_info b on a.classid= b.classid
group by a.id,a.classid,a.name,a.results
having count(case when a.results <= b.results then 1 else null end) < = 3 --可动态修改
order by a.id,a.results desc
drop table T_info/*id classid name results
----------- ----------- -------------------- -----------
3 1 aa 80
4 1 aa 90
5 1 aa 100
6 2 bb 85
8 2 bb 94
10 2 bb 87(所影响的行数为 6 行)*/
select id,classid,results,row_number() over(partition by classid order by results) ranks
from T_info)a
where ranks<=20
如果重复的也占名次,如第1和第2 人并列重复,则只有第一名和第三名,不设第二名,则改用Rankselect * from (
select id,classid,results,rank() over(partition by classid order by results) ranks
from T_info)a
where ranks<=20如果重复的不占名次,如第1和第2 人并列重复,则有两第一名,然后是第二名,则改用dense_Rankselect * from (
select id,classid,results,dense_rank() over(partition by classid order by results) ranks
from T_info)a
where ranks<=20
表T_info(id,classid,name,results) (id,班级ID,名字,成绩)
选出每个班级成绩前20名
----------------------------------------
select * from T_info a where id in
(select top 20 id from T_info where classid=a.classid order by name desc)
RANK 返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
DENSE_RANK 返回结果集分区中行的排名,在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。