8
回答
sql 查询问题
百度AI开发者大赛带你边学边开发,赢100万奖金,加群:418589053   

有一个表  表中有 学号,姓名,分数,课程。请问如何查出每个课程分数最高的两个学生,求大神赐教,只有这一张表!!!

表数据如下:

<无标签>
举报
msgyg
发帖于12个月前 8回/193阅

group by + join 是无法解决最高多个问题的。让我来写个神奇的sql……

select t1.* from 表 t1 

where ( select count(t2.id) from 表 t2 where t1.课程 = t2.课程 and t1.分数 < t2.分数 ) < 2;

 

--- 共有 13 条评论 ---
DeMoNHaDeS 回复 @izee : 并列情况下会大于2条。每个课程随机取两条写成两条sql,第一条DISTINCT或者group by查出所有课程,第二条select * from (select * from 表 where 课程 = '课程1' limit2 ) a1 union all ... 。如果要类似写成一条sql,排序之后利用mysql的@rowno或者oracle的rownum加行号类似 12个月前 回复
izee如果每个课程随机取两条怎么写? 12个月前 回复
izee666,不过目测在并列的情况下,会超出2条记录 12个月前 回复
DeMoNHaDeS 回复 @DeMoNHaDeS : 测了下不能写count(t2.*),直接写count(*) 12个月前 回复
DeMoNHaDeS 回复 @foy : 谢谢^_^ 12个月前 回复

分两步走吧,先查询有几门课程,然后再查询每门课程前两个学生,这样虽然sql多点,但是逻辑清楚,第二步如果想作死一次性查出来吧sql join下就可以了

select a.* from tb a,(select kecheng,max(fenshu) fenshu from tb group by kecheng) b where a.kecheng = b.kecheng and a.fenshu = b.fenshu order by a.kecheng ;

先找出每个科目分数最高的一条数据, 然后关联学生表,取到这个学生的姓名

咦?这怎么老像我给人家的SQL面试题……

--- 共有 2 条评论 ---
Kit_lee 回复 @msgyg : 有点儿不同吧,我给的题是只查最高分,容易些,答案如下:select b.* from (select max(score) t,course from score group by course) a,score b where a.t=b.score and a.course=b.course 你这边认真看了下后发现是查头两个,这就要另一种写法了,我得想想 12个月前 回复
msgyg那你应该会吧。。 12个月前 回复

假设表结构是酱紫:(不满足范式:))

select c.student_id,c.name,c.course,c.score 
FROM
(
select c1.student_id,c1.name,c1.course,c1.score,
(SELECT count(*) from course_score c2 where c2.score > c1.score and c2.student_id <> c1.student_id and c2.course = c1.course) as lagerCnt
from course_score c1
) as c
where c.lagerCnt < 2 ORDER BY c.course,c.score DESC

思路是先把自己连起来,count分数比自己大的个数,然后在筛一遍。。。

写得好复杂的样子:)

按照你更新问题给的表,可以这么写:

思路就是第一条SQL找到每门最高分,然后再union一条查询,将第一条SQL拼进去,用not in排除掉最高记录后用max再取一次第二高的记录

select * from score a where not exists (select * from score b where a.sco<b.sco and a.cname=b.cname) union
(select d.* from score d inner join (select max(c.sco) as sco,c.cname from score c where sno not in (
  select sno from score a where not exists (select * from score b where a.sco<b.sco and a.cname=b.cname)
) group by c.cname) e on d.sco=e.sco and d.cname=e.cname);

你这个表的结构就怪怪的,为什么不一溜写好呢?

 name  语文  数学

张三    98       89

张四    58        85

--- 共有 1 条评论 ---
古月哥欠666不然的话,就要连好多次 12个月前 回复
顶部