共享一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 编辑 ] 谢谢机楼主..
thanks
thanks very much 实在太好了 不知道我的对不,很久没写SQL了select top 2 *
from (select * from learn order by grade desc)
where kc in (select distinct (kc) from learn) select top 2 *
from (select * from learn order by kc, grade desc) a
where a.kc in (select distinct (kc) from learn) 楼主的SQL错了
selectstudent_id, kc,grade from (
selectstudent_id,kc,grade,row_number over (partition by kc order by grade desc) rn fromleanr
) wherern<=2 很不错 谁的语句是对的 3Q select b.student_id,b.kc,grade,b.number
from (select student_id,kc,grade,number=(select count(*) from leanr wherekc=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 编辑 ] 我感觉6楼的对的 我建过表验证过了,#11是对的。
回复 13# 的帖子
学习下:) 如果考虑到一样的分数,楼上的sql应该修改下select b.id,b.kc,grade,b.number
from (select id,kc,grade,number=(select count(*) from learn wherekc=t.kc and grade>t.grade )
from learn t) b
where b.number<=1
order by b.kc,b.grade desc,b.number
楼主给的语句在postgresql 试了一下。
ERROR: column "num" does not exist 在 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
可以完美解决相同分数的问题
页:
[1]