mysql查询两个表的数据库,关于mysql 数据库中的一个表以及两个查询

有一个这样的表 test1,问题如下:

1.如何查出每一门科目中分数最高的那位学生的姓名,课程以及分数?

2.如何查出每一门科目中分数排名前二的学生的姓名,课程以及分数?

好的,那么开始吧,感觉很基础嘛

1 .... 第一个问题:查出每一门科目中最大的一项, 想到两种方式

<1.1>

select *from( select * from test1 order by score desc) tgroup by course

效果是这样的

咦,想想这样确实不好,凭什么两个数学满分的,就给出来一个呀,另外一个呢,没办法,group by 一项的时候只会返回一项

<1.2>

select w.name,t.* from(select course,MAX(score) score from test1 group bycourse) tinner jointest1 won w.score=t.scoreand w.course=t.course

这一种用MAX(),外加内连接,算是达到效果了,两位满分的优秀同学也出来了,不过总感觉这样写太复杂了,然后一时半会也想不出什么更简单的

2...OK,第一个问题就暂且算是解决了,重头戏是第二个,

查出没门科目中分数最高的两位同学,

<2.1>第一反应是是TOP语句,奈何这是MYSQL,并不支持,那就想着用limit呗,

于是

select *

fromtest1 awhere 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 *

fromtest1 awhere id in (select id from (select id form test1 where course=a.course order by score desc limit 2)asnew)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 ) ASpaimingFROM test1 t1

直接先对每一个科目,进行一个排名,加一列  命名为paiming好了,结果如下,

虽然两位满分的学,的排名出了点小状况,但是就先不提了,假装没看到

然后再根据这个排名,筛选出前面两项,嗯嗯,看到这里,顿时觉得人家真是天才,然后

SELECT * FROM(SELECT t1.*,

(SELECT count( * ) FROM test1 t2 WHERE t1.score <= t2.score AND t1.course = t2.course ) aspaimingFROMtest1 t1

)ast3where paiming <=2

order by t3.course,t3.score desc

结果;

哎呀,我去,似乎没啥问题,,,  OK,OK,OK, 成功的说服了我自己

接下来,还有另外两种方式,

<2.3>

SELECTa.name,a.course,a.scoreFROMtest1 aLEFT JOIN test1 b ON a.course =b.courseAND a.score

a.course,

a.scoreHAVING

count( b.id ) <2

order by a.course,a.score desc

OJBK  !   可是为嘛要搞这么复杂呢,简简单单多开心 所以下面这种方式就很简单了

<2.4>

select *

fromtest1 twhere(select count(*) fromtest1where course=t.courseand score>t.score)< 2

order by t.course,t.score desc

依然没毛病,开开心心

原文：https://www.cnblogs.com/yanmouren/p/10478704.html