题目是:有个表leanr 表里有三个字段学生ID(name)课程(course)成绩(grade)清写一条查询语句搜
索每一门课程的前2名
本人写了一个巨菜的,结果出来了,但觉得不至于这么繁琐,忘有思路的同仁说说自己的答案,我学习学习,谢谢了:
(select aa.course,max(aa.grade) grade From student aa,(select a.course,b.grade
FROM (select distinct course from student group by course) a,
(select course,max(grade) sorce from student group by course) b
WHERE a.course = b.course) bb
where aa.course = bb.course
and aa.grade != bb.grade
group by aa.course
union all
select a.course,b.grade from (select distinct course from student group by course) a,
(select course,max(grade) sorce from student group by course) b
where a.course=b.course) bbb
where aaa.course = bbb.course;
索每一门课程的前2名
本人写了一个巨菜的,结果出来了,但觉得不至于这么繁琐,忘有思路的同仁说说自己的答案,我学习学习,谢谢了:
(select aa.course,max(aa.grade) grade From student aa,(select a.course,b.grade
FROM (select distinct course from student group by course) a,
(select course,max(grade) sorce from student group by course) b
WHERE a.course = b.course) bb
where aa.course = bb.course
and aa.grade != bb.grade
group by aa.course
union all
select a.course,b.grade from (select distinct course from student group by course) a,
(select course,max(grade) sorce from student group by course) b
where a.course=b.course) bbb
where aaa.course = bbb.course;
from
(
select a.*,row_number() over (partition by course order by grade desc) rn
from leanr
) aa
where aa.rn<3
from
(
select a.*,row_number() over (partition by course order by grade desc) rn
from leanr a
) aa
where aa.rn <3
select *(
select l.*
,row_number() over(partition by course order by grade) rn
from leanr l) where rn<=2