查看完整版本: 共享一SQL面试题

dandan 2007-4-12 11:49

共享一SQL面试题

有一个表LEANR，表里有三个字段分别是学生ID（student_id），课程（kc）,成绩（grade），要求用一条查询语句搜索出每一门课程的前两名。
答案：select student_id,kc,grade from (select student_id,kc,grade,number=(select count(distinct grade) from learn where t.kc=kc and t.grade<grade) from learn t ) tt where number>2

[[i] 本帖最后由 dandan 于 2007-4-12 12:18 编辑 [/i]]

wangvivian 2007-4-18 16:13

谢谢机楼主..

irya@163.com 2007-4-25 13:55

thanks

thanks very much

songliu8410 2007-9-20 19:37

实在太好了

anTidd 2007-9-30 22:38

不知道我的对不，很久没写SQL了
select top 2 *
from (select * from learn order by grade desc)
where kc in (select distinct (kc) from learn)

anTidd 2007-9-30 22:44

select top 2 *
from (select * from learn order by kc, grade desc) a
where a.kc in (select distinct (kc) from learn)

bleu 2007-10-11 09:54

楼主的SQL错了

select  student_id, kc,grade from (
select  student_id,kc,grade,row_number over (partition by kc order by grade desc) rn from  leanr
) where  rn<=2

aqwdao 2007-10-19 08:54

很不错

zhjjtmcn 2007-10-28 01:49

谁的语句是对的

wei_jc 2007-11-1 13:05

3Q

Helen_px 2007-11-7 11:02

select b.student_id,b.kc,grade,b.number
from (select student_id,kc,grade,number=(select count(*) from leanr where  kc=t.kc and grade>=t.grade )
      from leanr t) b
where b.number<=2
order by b.kc,b.grade desc,b.number

[[i] 本帖最后由 Helen_px 于 2007-11-7 11:32 编辑 [/i]]

yuxuan555269 2007-11-21 15:02

我感觉6楼的对的

软件测试论坛 2008-3-18 14:08

我建过表验证过了，#11是对的。

williamcool 2008-3-19 14:27

回复 13# 的帖子

学习下:)
页: [1]
查看完整版本: 共享一SQL面试题