一句高难度的T-ＳＱＬ
表如下所示
学号 课程 成绩
001 语文 82
001 数学 92
002 英语 66
002 物理 99
003 .....
.....
请使用一句sql语句，求每门课程第3名的学生的学号、课程、成绩
[解决办法]
有一表
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
declare @tab table(a int,b varchar(2),c int)
insert @tab values(7, 'aa ',153)
insert @tab values(9, 'aa ',152)
insert @tab values(6, 'aa ',120)
insert @tab values(8, 'aa ',168)
insert @tab values(5, 'bb ',159)
insert @tab values(7, 'bb ',179)
insert @tab values(8, 'bb ',149)
insert @tab values(9, 'bb ',139)
insert @tab values(6, 'bb ',169)
select * from @tab
select px=(select count(1) from @tab where b=a.b and c <a.c)+1 , a,b,c from @tab a
order by b , c
px a b c
----------- ----------- ---- -----------
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
（所影响的行数为 9 行）
在上面例中我们看到，以B分类排序，C是从小到大，如果C从大到小排序，即下面结果：
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
declare @tab table(a int,b varchar(2),c int)
insert @tab values(7, 'aa ',153)
insert @tab values(9, 'aa ',152)
insert @tab values(6, 'aa ',120)
insert @tab values(8, 'aa ',168)
insert @tab values(5, 'bb ',159)
insert @tab values(7, 'bb ',179)
insert @tab values(8, 'bb ',149)
insert @tab values(9, 'bb ',139)
insert @tab values(6, 'bb ',169)
select * from @tab
select px=(select count(1) from @tab where b=a.b and c> a.c)+1 , a,b,c from @tab a
order by b , c desc
px a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
（所影响的行数为 9 行）
[解决办法]
select * from
(
select px=(select count(1) from tb where 课程=a.课程 and 成绩> a.成绩)+1 , 学号,课程,成绩 from tb a
) t
where px = 3
[解决办法]
select
t.*
from
表 t
where
t.学号 in(select top 3 学号 from 课程=t.课程 order by 成绩 desc)
[解决办法]
1、假设原表如下为A：
学号 课程 成绩
001 语文 100
001 数学 98
002 语文 98
002 数学 100
003 语文 99
003 数学 99
----------------------------------------------
2、表A按课程和成绩排序，添加一些字段生成临时表#A
select identity(int,1,1) SN,学号,课程,成绩,0 排名 into #A from A order by 课程,成绩
执行结果：
SN学号 课程 成绩排名
1001 语文 1000
2003 语文 990
3002 语文 980
4002 数学 1000
5003 数学 990
6001 数学 980
-----------------------------------------------
3、根据表#A求得各课程的头行SN值，并生成临时表#B
select Min(SN) MinSN,课程 into #B from #A group by 课程
执行结果：
MinSN课程
1语文
4数学
-----------------------------------------------
4、根据表#B，更新表#A求得各科排名
update #A set 排名=SN-B.MinSN+1
from #A A
inner join #B B on A.课程=B.课程
执行结果表#A：
SN学号 课程 成绩排名
1001 语文 1001
2003 语文 992
3002 语文 983
4002 数学 1001
5003 数学 992
6001 数学 983
------------------------------------------------
5、查询求得各课排第3的记录
select * from #A where 排名=3
执行结果如下：
SN学号 课程 成绩排名
3002 语文 983
6001 数学 983