一、建表造數(shù)據(jù)

建表：

create table scores( name varchar(100), subject varchar(100), score int );

插入數(shù)據(jù)：

insert into scores values ('學(xué)生a','java','100'), ('學(xué)生b','java','90'), ('學(xué)生c','java','90'), ('學(xué)生d','java','60'), ('學(xué)生e','java','80'), ('學(xué)生a','python','100'), ('學(xué)生b','python','90'), ('學(xué)生c','python','90'), ('學(xué)生d','python','60'), ('學(xué)生e','python','80');

二、使用myql查詢

2.1 不考慮并列情況

方法一：使用加行號(hào)的方式查詢

加行號(hào)的使用規(guī)則是：@rowNum:=num意思是聲明一個(gè)叫 rowNum 的變量并賦值為num示例： 現(xiàn)在有一個(gè)需求是： “查詢學(xué)生 java 課的成績(jī)、姓名并排名？”

select score,name,@m1:=@m1+1 r from scores,(select @m1:=0)a where subject='java' order by score desc 1

查詢結(jié)果如下

此處加行號(hào)m1的作用就可以體現(xiàn)出來(lái)，查詢語(yǔ)句中多了一個(gè)字段 r ，他可以以數(shù)字1,2,3,4,5的形式顯示排名

由此引申，此條查詢語(yǔ)句結(jié)尾在加上 limit 3 便可以取出前三名

select score,name,@m1:=@m1+1 r from scores,(select @m1:=0)a where subject='java' order by score desc limit 3

再引申，若要查詢所有課程的成績(jī)，取前三名，則就需要將其他的課程表 join 在一起，關(guān)聯(lián)條件為每條查詢語(yǔ)句的行號(hào)相等

select s1.score "java成績(jī)",s1.name,s2.score "python成績(jī)",s2.name,s1.r "排名" from (select score,name,@m1:=@m1+1 r from scores,(select @m1:=0)a where subject='java' order by score desc limit 3)s1 join (select score,name,@m2:=@m2+1 r from scores,(select @m2:=0)b where subject='python' order by score desc limit 3)s2 on s1.r=s2.r;

輸出結(jié)果為：

這種方法查詢實(shí)際上是 列轉(zhuǎn)行 的方式，將字段subject 列 轉(zhuǎn)成 行 輸出。優(yōu)點(diǎn) 是容易理解，增加了一個(gè)字段顯示排名，更加直觀。缺點(diǎn) 是在關(guān)聯(lián)條件多(比如課程數(shù)量大于10，查詢每科前10名，前20名成績(jī)…)的情況下， join 關(guān)聯(lián)10次以上，頻繁的join會(huì)損耗系統(tǒng)很多性能，嚴(yán)重的會(huì)直接堵塞死。且有個(gè)弊端是寫查詢語(yǔ)句的時(shí)候必須要知道具體有幾門課及課程名稱，where 條件就已經(jīng)限定了每門課的課程id或者課程名稱，但有些情況下表數(shù)據(jù)量很大的時(shí)候，這種方法是不合適的。

方法二：使用子查詢嵌套查詢（使用最多）

select s1.* from scores s1 where (select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score)<3 order by s1.subject,s1.score desc;

查詢結(jié)果如下：

這種方法比較難懂，但是查詢速度快且代碼簡(jiǎn)單，解析如下：

首先如果要查詢所有課程的成績(jī)，如下：

再對(duì)各科成績(jī)倒序排列輸出：

然后 取各科成績(jī)前三名，就需要嵌套子查詢進(jìn)行篩選，代碼如下：

select s1.* from scores s1 where (select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score)<3 order by s1.subject,s1.score desc;

查詢語(yǔ)句重點(diǎn)在于 -->子查詢語(yǔ)句： select count(1) from scores s2 where s1.subject=s2.subject and s1.score<s2.score意思是：統(tǒng)計(jì)學(xué)生個(gè)數(shù)，即關(guān)聯(lián)兩個(gè)分?jǐn)?shù)表s1、s2，外層查詢每查詢一次，再到內(nèi)層循環(huán)中查詢表s2，當(dāng)課程名相同時(shí)，統(tǒng)計(jì) s1.score<s2.score 即表s2中成績(jī)大于s1的人數(shù)有幾人。這么說(shuō)可能還是有點(diǎn)懵，現(xiàn)在我們來(lái)走一遍流程，從頭到尾依次遍歷一次，詳細(xì)說(shuō)明：

首先從學(xué)生a開始查詢：

→學(xué)生b：

→學(xué)生c：

→學(xué)生d：

→學(xué)生e：

此時(shí)從學(xué)生a到e的 “java” 課程前三名已經(jīng)篩選完成，對(duì)于 “python” 課程，重復(fù)上述流程即可全部篩選完畢最后再對(duì)查詢出的課程、分?jǐn)?shù)倒序排列即可 ：order by s1.subject,s1.score desc

2.2 考慮并列情況

select s1.name,s1.subject,s1.score from scores s1 left join (select distinct subject,score from scores) s2 on s1.subject=s2.subject and s1.score<s2.score group by s1.name,s1.subject,s1.score having count(1)<3 order by subject,score desc;

查詢結(jié)果如下：

很直觀的可以看出，學(xué)生b和c成績(jī)都為90分，并列第二名，學(xué)生e成績(jī)80分，為第三名

查詢語(yǔ)句解析：這是在 2.1方法二 的基礎(chǔ)上，使用 distinct 關(guān)鍵字對(duì)表s2中存在多名同學(xué)分?jǐn)?shù)相同的情況進(jìn)行 去重，從而達(dá)到并列排名的目的。需要注意的是，由于groub by 的條件是表s1中的字段，所以 count(1) 統(tǒng)計(jì)的是表s1中每次查詢s1.score<s2.score 成績(jī)低于表s2的學(xué)生人數(shù)，滿足條件小于3，就可以取出前三名

三、使用hive查詢前三名

使用hive查詢需要調(diào)用 窗口函數(shù)，類似于 2.1方法一 中加行號(hào)查詢，但使用不同的窗口函數(shù)可以實(shí)現(xiàn)并列與不并列的排名順序

3.1 不考慮并列情況:rank()

select * from (select name,subject,score,rank() over (partition by subject order by score desc) ranks from scores)s where ranks<4;

查詢結(jié)果如下：

3.2 考慮并列情況:dense_rank()

select * from (select name,subject,score,dense_rank() over (partition by subject order by score desc) ranks from scores)s where ranks<4;

查詢結(jié)果如下：

是不是非常簡(jiǎn)單！

總結(jié)

以上是生活随笔為你收集整理的sql查询成绩最高分_sql查询各科成绩前三名----详述过程，思路清晰不烧脑！的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。