有一个这样的表 test1,问题如下:
1.如何查出每一门科目中分数最高的那位学生的姓名,课程以及分数?
2.如何查出每一门科目中分数排名前二的学生的姓名,课程以及分数?
好的,那么开始吧,感觉很基础嘛
1 .... 第一个问题:查出每一门科目中最大的一项, 想到两种方式
<1.1>
select *from( select * from test1 order by score desc) t group by course
效果是这样的
咦,想想这样确实不好,凭什么两个数学满分的,就给出来一个呀,另外一个呢,没办法,group by 一项的时候只会返回一项
<1.2>
select w.name,t.* from (select course,MAX(score) score from test1 group by course) t inner join test1 w on w.score=t.score and w.course=t.course
这一种用MAX(),外加内连接,算是达到效果了,两位满分的优秀同学也出来了,不过总感觉这样写太复杂了,然后一时半会也想不出什么更简单的
2...OK,第一个问题就暂且算是解决了,重头戏是第二个,
查出没门科目中分数最高的两位同学,
<2.1>第一反应是是TOP语句,奈何这是MYSQL,并不支持,那就想着用limit呗,
于是
select * from test1 a where id in (select id from test1 where course=a.course order by score desc limit 2) order by a.course,a.score desc
嗯嗯,思路很清晰,奈何造化弄人,根本就是会报错的,高版本的mysql 中 in 里面并不能有 limit,于是啊,那就看看别人是怎么解决这个的,
果然, 说是在包一个 子表 就能解决这个, 于是我
select * from test1 a where id in (select id from (select id form test1 where course=a.course order by score desc limit 2)as new) order by a.course,a.score desc
哎呀,好烦呀,还是报错的,,,,,,那就先不用这种方式了,
<2.2>后面 无意间又在别人的博客里看到另一种方式,这个思路就更加清晰了,大概是这样做的,首先
SELECT t1.*, ( SELECT count( * ) FROM test1 t2 WHERE t1.score <=t2.score AND t1.course = t2.course ) AS paiming FROM test1 t1
直接先对每一个科目,进行一个排名,加一列 命名为paiming好了,结果如下,
虽然两位满分的学,的排名出了点小状况,但是就先不提了,假装没看到
然后再根据这个排名,筛选出前面两项,嗯嗯,看到这里,顿时觉得人家真是天才,然后
SELECT * FROM (SELECT t1.*, ( SELECT count( * ) FROM test1 t2 WHERE t1.score <= t2.score AND t1.course = t2.course ) as paiming FROM test1 t1 ) as t3 where paiming <=2 order by t3.course,t3.score desc
结果;
哎呀,我去,似乎没啥问题,,, OK,OK,OK, 成功的说服了我自己
接下来,还有另外两种方式,
<2.3>
SELECT a.name,a.course,a.score FROM test1 a LEFT JOIN test1 b ON a.course = b.course AND a.score < b.score GROUP BY a.NAME, a.course, a.score HAVING count( b.id ) <2 order by a.course,a.score desc
OJBK ! 可是为嘛要搞这么复杂呢,简简单单多开心 所以下面这种方式就很简单了
<2.4>
select * from test1 t where (select count(*) from test1 where course=t.course and score>t.score)< 2 order by t.course,t.score desc
依然没毛病,开开心心
原文：https://www.cnblogs.com/yanmouren/p/10478704.html