标题: 共享一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
[ 本帖最后由 dandan 于 2007-4-12 12:18 编辑 ]作者: 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
[ 本帖最后由 Helen_px 于 2007-11-7 11:32 编辑 ]作者: yuxuan555269 时间: 2007-11-21 15:02
我感觉6楼的对的作者: 软件测试论坛 时间: 2008-3-18 14:08
我建过表验证过了，#11是对的。作者: williamcool 时间: 2008-3-19 14:27 标题: 回复 13# 的帖子 学习下作者: 李玮123456 时间: 2016-11-21 14:59
如果考虑到一样的分数，楼上的sql应该修改下
select b.id,b.kc,grade,b.number
from (select id,kc,grade,number=(select count(*) from learn where kc=t.kc and grade>t.grade )
from learn t) b
where b.number<=1
order by b.kc,b.grade desc,b.number 作者: kfc427 时间: 2016-11-30 15:37
楼主给的语句在postgresql 试了一下。
ERROR: column "num" does not exist作者: kfc427 时间: 2016-11-30 16:36
在 postgresql 试了一下
select course,student,grade,row_id from
(select *,row_number() over (partition by course order by grade desc) as row_id from learn) as l where row_id<3 order by course
