某公司笔试题一道，寻求答案

[chongmo02]

tb_student表有两个字段name和score，写SQL语句实现将表中分数第二的学生的名字显示出来。

[rucypli]

select *
from tb_student
where score <> (select max(score) from tb_student)

[rucypli]

select *
from tb_student T1
where not exists (select 1 from tb_student T2 where T1.score<T2.score)

[htl258_Tony]

select top 1 * from 
(select top 2 *
from tb_student
order by score desc ) t
order by score 

[SQLnewlearner]

select top 1 * from (select top 2 from tb order by score desc)a order by score asc

[zhuxianzhu]

select top 1 * from (select top 2 from tb order by score desc)a order by score asc

[jwdream2008]

[Quote=引用 3 楼 htl258 的回复:]
SQL codeselect top 1 * from
(select top 2 *
from tb_student
order by score desc ) t
order by score
[/Quote]

正解！

[wen1512]

select top 1 *
from 
(select * from tb_student
where score < (select max(score) 
order by desc 
)

[chinesesword]

select
*
from
(
select name,score,dense_rank() over(order by score) as ord
)tmp
where tmp.ord = 2

[chinesesword]

[Quote=引用 8 楼 chinesesword 的回复:]
select
*
from
(
select name,score,dense_rank() over(order by score) as ord
)tmp
where tmp.ord = 2
[/Quote]
少写个表

select
*
from
(
select name,score,dense_rank() over(order by score) as ord from tb_student

)tmp
where tmp.ord = 2

[chongmo02]

各位谢谢你们了，我第一次在上面发帖，也不知道这里的行情，给分有不妥之处还望多多包涵啦~~~~
PS：题目上是说分数第二的学生的“名字”显示出来~~~~我回去把你们的答案研究下

[resoft]

[Quote=引用 3 楼 htl258 的回复:]
SQL codeselect top 1 * from
(select top 2 *
from tb_student
order by score desc ) t
order by score
[/Quote]

select top 1 name from (select top 2 * from student order by score desc) tt 
order by score asc