第一名 select top 1 * from tablename order by 成绩第二三名 select top 2 * from tablename where id not in (select top 1 id from tablename order by 成绩) order by 成绩第四五六名 select top 3 * from tablename where id not in (select top 3 id from tablename order by 成绩) order by 成绩
create table test( score int, stuname varchar(10) ) insert into test select 90,'A' union all select 88,'B' union all select 93,'C' union all select 63,'D' union all select 78,'E' union all select 82,'F' union all select 75,'G' union all select 88,'H' go------第一名------- select top 1 with ties * from test order by score desc ------第二三名--- select top 2 with ties * from test where score<(select top 1 score from test order by score desc) order by score desc ------第四五六名--------- select top 3 with ties * from test where score<(select min(score) from (select top 3 score from test order by score desc) as t) order by score descdrop table test
Haiwer(海阔天空) 那样做不好!!
create table #test( score int, stuname varchar(10) ) insert into #test select 90,'A' union all select 88,'B' union all select 93,'C' union all select 63,'D' union all select 78,'E' union all select 82,'F' union all select 75,'G' union all select 93,'H' select a.stuname[学生],max(a.score)[分数],count(b.stuname)+1[名次] from #test a left join #test b on b.score>a.score group by a.stuname --要有选择取学生信息可以加having count(b.stuname)+1=名次 order by count(b.stuname)学生 分数 名次 ---------- ----------- ----------- C 93 1 H 93 1 A 90 3 B 88 4 F 82 5 E 78 6 G 75 7 D 63 8
select top 1 * from tablename order by 成绩第二三名
select top 2 * from tablename where id not in (select top 1 id from tablename order by 成绩)
order by 成绩第四五六名
select top 3 * from tablename where id not in (select top 3 id from tablename order by 成绩)
order by 成绩
score int,
stuname varchar(10)
)
insert into test
select 90,'A' union all
select 88,'B' union all
select 93,'C' union all
select 63,'D' union all
select 78,'E' union all
select 82,'F' union all
select 75,'G' union all
select 88,'H'
go------第一名-------
select top 1 with ties * from test order by score desc
------第二三名---
select top 2 with ties * from test where score<(select top 1 score from test order by score desc) order by score desc
------第四五六名---------
select top 3 with ties * from test where score<(select min(score) from (select top 3 score from test order by score desc) as t) order by score descdrop table test
那样做不好!!
score int,
stuname varchar(10)
)
insert into #test
select 90,'A' union all
select 88,'B' union all
select 93,'C' union all
select 63,'D' union all
select 78,'E' union all
select 82,'F' union all
select 75,'G' union all
select 93,'H'
select a.stuname[学生],max(a.score)[分数],count(b.stuname)+1[名次]
from #test a
left join #test b
on b.score>a.score
group by a.stuname --要有选择取学生信息可以加having count(b.stuname)+1=名次
order by count(b.stuname)学生 分数 名次
---------- ----------- -----------
C 93 1
H 93 1
A 90 3
B 88 4
F 82 5
E 78 6
G 75 7
D 63 8