本文中介紹的是MySQL經(jīng)典50題的第21-25題目，主要涉及的知識(shí)點(diǎn)包含：

• 分組統(tǒng)計(jì)求和，百分比

• 如何利用SQL實(shí)現(xiàn)排序

• having使用

• union拼接

5個(gè)題目是：

• 查詢不同老師所教不同課程平均分從高到低顯示

• 查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績

• 統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比

• 查詢學(xué)生的平均成績及名次

• 查詢各科成績前三名的記錄

題目21

題目需求

查詢不同老師所教不同課程平均分從高到低顯示

分析過程

涉及到的表主要是

老師：Teacher

課程：Course，作為主表

成績：Score

通過3個(gè)表的連接求出來即可

SQL實(shí)現(xiàn)

先找出每個(gè)老師教授了哪些課程：

select?c.c_name,t.t_name from?Course?c left?join?Teacher?t on?c.t_id?=?t.t_id;

將上面的結(jié)果和成績表連接起來：

select?c.c_name,t.t_name,round(avg(s.s_score),2)??score???--?課程分組后再求均值 from?Course?c???--?主表，通過兩次連接 left?join?Teacher?t on?c.t_id?=?t.t_id left?join?Score?s on?c.c_id?=?s.c_id group?by?c.c_id???--?課程分組 order?by?3?desc;??--?降序

題目22

題目需求

查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績

分析過程

成績：Score

學(xué)生信息：Student

我們通過取出每科的第2、3名拼接起來再取出學(xué)生信息

SQL實(shí)現(xiàn)

自己的方法

1、課程表和成績表連接起來，顯示所有的課程和成績信息

select?s.s_id,s.c_id,s.s_score,c.c_name from?Score?s join?Course?c on?s.c_id?=?c.c_id

2、查出全部的語文成績

select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc;

3、我們找出語文的第2、3的學(xué)生

select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc limit?1,?2;

4、同時(shí)求出語文、數(shù)學(xué)、英語的分?jǐn)?shù)，并且通過union拼接

--?union連接(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc limit?1,?2)union(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'數(shù)學(xué)' order?by?s.s_score?desc limit?1,?2)union ((select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'英語' order?by?s.s_score?desc limit?1,?2))

5、將上面的結(jié)果學(xué)生信息表進(jìn)行連接即可

好歹是實(shí)現(xiàn)了????

--?最終腳本 --?！！！！真的需要好好優(yōu)化下selects.s_id,s.s_name,t.c_name,t.s_score from?Student?s join?(--?union連接(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc limit?1,?2)union(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'數(shù)學(xué)' order?by?s.s_score?desc limit?1,?2)union ((select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'英語' order?by?s.s_score?desc limit?1,?2)))t??--?臨時(shí)表ton?s.s_id?=?t.s_id

和第25題相同的方法

1、以語文為例，首先我們找出前3名的成績（包含相同的成績）

--?語文 selecta.s_id,a.c_id,a.s_score???--?3、此時(shí)a表的成績就是我們找的?? from?Score?a join?Score?b on?a.c_id?=?b.c_id and?a.s_score?<=?b.s_score??--?1、判斷a的分?jǐn)?shù)小于等于b的分?jǐn)?shù)，要帶上等號(hào) and?a.c_id="01" group?by?1,2 having?count(b.s_id)?<=?3??--?2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對(duì)分?jǐn)?shù)相同的情形 order?by?3?desc limit?1,2 --?語文 selecta.s_id,a.c_id,a.s_score???--?3、此時(shí)a表的成績就是我們找的?? from?Score?a join?Score?b on?a.c_id?=?b.c_id and?a.s_score?<=?b.s_score??--?1、判斷a的分?jǐn)?shù)小于等于b的分?jǐn)?shù)，要帶上等號(hào) and?a.c_id="01" group?by?1,2 having?count(b.s_id)?<=?3??--?2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對(duì)分?jǐn)?shù)相同的情形 order?by?3?desc limit?1,2;???--?取得第2、3名

在通過數(shù)學(xué)和英語的類似操作得到2、3名的成績，再進(jìn)行拼接即可

題目23

題目需求

統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比

分析過程

課程：Course

成績：Score

通過case語句來進(jìn)行判斷，count語句進(jìn)行統(tǒng)計(jì)，sum語句進(jìn)行求和

SQL實(shí)現(xiàn)

自己的方法

1、如何對(duì)每個(gè)成績進(jìn)行分組展示：ABCD代表相應(yīng)的等級(jí)

select?c_id,s_score,case?when?s_score?>=?85?and??s_score<=?100?then?'A'???--?大小關(guān)系必須分兩次寫，一次寫的話MySQL無法識(shí)別when?70?<=?s_score?and?s_score?<?85?then?'B'when?60?<=?s_score?and?s_score?<?70?then?'C'when?0?<=?s_score?and?s_score?<?60?then?'D'else?'其他'?end?as?'category' from?Score?s;

2、將兩個(gè)表關(guān)聯(lián)起來展示數(shù)據(jù)

--?1、查看全部課程和成績信息select?s.c_id,c.c_name,s.s_score,case?when?s.s_score?>=?85?and??s.s_score<=?100?then?'A'???--?大小關(guān)系必須分兩次寫，一次寫的話MySQL無法識(shí)別when?70?<=?s.s_score?and?s.s_score?<?85?then?'B'when?60?<=?s.s_score?and?s.s_score?<?70?then?'C'when?0?<=?s.s_score?and?s.s_score?<?60?then?'D'else?'其他'?end?as?'category' from?Score?s join?Course?c? on?s.c_id?=?c.c_id;

3、完整代碼

selects.c_id??編號(hào),c.c_name??科目,sum(case?when?s.s_score?>=?85?and??s.s_score<=?100?then?1?else?0?end)?"[85,100]人數(shù)",round(100?*?(sum(case?when?s.s_score?>=?85?and??s.s_score<=?100?then?1?else?0?end)?/?sum(case?when?s.s_score?then?1?else?0?end)),?2)?as?'[85,100]百分比',sum(case?when?s.s_score?>=?70?and??s.s_score<=?85?then?1?else?0?end)?"[70,85]人數(shù)",round(100?*?(sum(case?when?s.s_score?>=?70?and??s.s_score<=?85?then?1?else?0?end)?/?sum(case?when?s.s_score?then?1?else?0?end)),?2)?as?'[70,85]百分比',sum(case?when?s.s_score?>=?60?and??s.s_score<=?70?then?1?else?0?end)?"[60,70]人數(shù)",round(100?*?(sum(case?when?s.s_score?>=?60?and??s.s_score<=?70?then?1?else?0?end)?/?sum(case?when?s.s_score?then?1?else?0?end)),?2)?as?'[60,70]百分比',sum(case?when?s.s_score?>=?0?and??s.s_score<=?60?then?1?else?0?end)?"[0,60]人數(shù)",round(100?*?(sum(case?when?s.s_score?>=?0?and??s.s_score<=?60?then?1?else?0?end)?/?sum(case?when?s.s_score?then?1?else?0?end)),?2)?as?'[0,60]百分比' from?Score?s left?join?Course?c on?s.c_id?=?c.c_id group?by?s.c_id,?c.c_name

參考方法

1、先統(tǒng)計(jì)每個(gè)階段的人數(shù)和占比

select?c_id,sum(case?when?s_score?>?85?and?s_score?<=100?then?1?else?0?end)?as?'85-100',round(100?*?(sum(case?when?s_score?>?85?and?s_score?<=?100?then?1?else?0?end)?/?count(*)),?2)?'占比' from?Score? group?by?c_id;??--?分課程統(tǒng)計(jì)總數(shù)和占比--?方式2 select?c_id,sum(case?when?s_score?>?85?and?s_score?<=100?then?1?else?0?end)?as?'85-100',round(100?*?(sum(case?when?s_score?>?85?and?s_score?<=?100?then?1?else?0?end)?/?count(case?when?s_score?then?1?else?0?end)),?2)?'占比'???--?不同count(*) from?Score? group?by?c_id;??

注意對(duì)比：

2、我們將4種情況同時(shí)查出來

select?c_id,sum(case?when?s_score?>?85?and?s_score?<=100?then?1?else?0?end)?as?'85-100',round(100?*?(sum(case?when?s_score?>?85?and?s_score?<=?100?then?1?else?0?end)?/?count(*)),?2)?'[85,100]占比',sum(case?when?s_score?>?70?and?s_score?<=85?then?1?else?0?end)?as?'70-85',round(100?*?(sum(case?when?s_score?>?70?and?s_score?<=?85?then?1?else?0?end)?/?count(*)),?2)?'[70,85]占比',sum(case?when?s_score?>?60?and?s_score?<=70?then?1?else?0?end)?as?'60-70',round(100?*?(sum(case?when?s_score?>?60?and?s_score?<=?70?then?1?else?0?end)?/?count(*)),?2)?'[60,70]占比',sum(case?when?s_score?>=0?and?s_score?<=60?then?1?else?0?end)?as?'0-60',round(100?*?(sum(case?when?s_score?>?0?and?s_score?<=?60?then?1?else?0?end)?/?count(*)),?2)?'[0,60]占比' from?Score? group?by?c_id;??--?分課程統(tǒng)計(jì)總數(shù)和占比

3、將科目名稱連接起來

--?整體和自己的方法是類似的 select?s.c_id,c.c_name,sum(case?when?s_score?>?85?and?s_score?<=100?then?1?else?0?end)?as?'85-100',round(100?*?(sum(case?when?s_score?>?85?and?s_score?<=?100?then?1?else?0?end)?/?count(*)),?2)?'[85,100]占比',sum(case?when?s_score?>?70?and?s_score?<=85?then?1?else?0?end)?as?'70-85',round(100?*?(sum(case?when?s_score?>?70?and?s_score?<=?85?then?1?else?0?end)?/?count(*)),?2)?'[70,85]占比',sum(case?when?s_score?>?60?and?s_score?<=70?then?1?else?0?end)?as?'60-70',round(100?*?(sum(case?when?s_score?>?60?and?s_score?<=?70?then?1?else?0?end)?/?count(*)),?2)?'[60,70]占比',sum(case?when?s_score?>=0?and?s_score?<=60?then?1?else?0?end)?as?'0-60',round(100?*?(sum(case?when?s_score?>?0?and?s_score?<=?60?then?1?else?0?end)?/?count(*)),?2)?'[0,60]占比' from?Score?s left?join?Course?c on?s.c_id?=?c.c_id group?by?s.c_id,?c.c_name;????--?分課程統(tǒng)計(jì)總數(shù)和占比

題目24

題目需求

查詢學(xué)生的平均成績及名次

分析過程

學(xué)生：Student

成績：Score

平均：avg函數(shù)

名次：通過排序來解決

SQL實(shí)現(xiàn)

自己的方法

1、先求出每個(gè)人的平均分

--?自己的方法select?sc.s_id,s.s_name,round(avg(sc.s_score),2)??avg_score from?Score?sc join?Student?s on?sc.s_id=s.s_id group?by?sc.s_id,s.s_name

2、我們對(duì)上面的結(jié)果進(jìn)行排序

！！！MySQL5中是沒有rank函數(shù)的，需要自己實(shí)現(xiàn)排序功能

--?MYSQL5.7中沒有rank函數(shù)，所以通過自連接實(shí)現(xiàn)selectt1.s_id,t1.s_name,t1.avg_score,(select?count(distinct?t2.avg_score)?from?(select?sc.s_id,s.s_name,round(avg(sc.s_score),2)??avg_scorefrom?Score?scjoin?Student?son?sc.s_id=s.s_idgroup?by?sc.s_id,s.s_name)t2????--?臨時(shí)表t2也是上面的結(jié)果where?t2.avg_score?>=?t1.avg_score)?rankfrom?(select?sc.s_id,s.s_name,round(avg(sc.s_score),2)??avg_scorefrom?Score?scjoin?Student?son?sc.s_id=s.s_idgroup?by?sc.s_id,s.s_name)t1???--?臨時(shí)表t1就是上面的結(jié)果 order?by?t1.avg_score?desc;

參考方法

selecta.s_id??--?學(xué)號(hào),@i:=@i+1?as?'不保留空缺排名'???--?直接i的自加,@k:=(case?when?@avg_score=a.avg_s?then?@k?else?@i?end)?as?'保留空缺排名',@avg_score:=avg_s?as?'平均分'??--?表a中的值from?(select?s_id,round(avg(s_score),?2)?as?avg_sfrom?Score?group?by?s_idorder?by?2?desc)a????--?表a：平均成績的排序和學(xué)號(hào),(select?@avg_score:=0,?@i:=0,?@k:=0)b???--?表b：通過變量設(shè)置初始值

實(shí)現(xiàn)rank函數(shù)

select?s.s_name??--?姓名,s.s_score??--?成績,(select?count(distinct?t2.s_score)from?Score?t2where?t2.s_score?>=?t1.s_score)?rank???--?在t2分?jǐn)?shù)大的情況下，統(tǒng)計(jì)t2的去重個(gè)數(shù) from?Score?t1 order?by?t1.s_score?desc;???--?分?jǐn)?shù)降序排列

舉例子來說明這個(gè)腳本：

姓名成績

張三	89
李四	90
王五	78
小明	98
小紅	60

當(dāng)t1.s_score=89，滿足t2.s_score > = t1.s_score的有98，90和89，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是3

當(dāng)t1.s_score=90，滿足t2.s_score > = t1.s_score的有98和90，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是2

當(dāng)t1.s_score=78，滿足t2.s_score > = t1.s_score的有98、90、89和78，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是4

當(dāng)t1.s_score=98，滿足t2.s_score > = t1.s_score的只有98，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是1

當(dāng)t1.s_score=60，滿足t2.s_score > = t1.s_score的有89、90、78、98、60，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是5

通過上面的步驟，我們發(fā)現(xiàn)：t1中每個(gè)分?jǐn)?shù)對(duì)應(yīng)的個(gè)數(shù)就是它的排名

題目25

題目需求

查詢各科成績前三名的記錄

分析過程

這題和第22題是屬于一個(gè)類型的：找到每個(gè)科目的指定名次的成績，使用的表是：Score

SQL實(shí)現(xiàn)

自己的方法

1、首先我們找出語文的前3名

select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc???--?降序之后取出前3條記錄 limit?3;

2、通過同樣的方法我們可以求出數(shù)學(xué)和英語的前3條記錄，然后通過union進(jìn)行聯(lián)結(jié)，有待優(yōu)化????

--?自己的腳本(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'語文' order?by?s.s_score?desc???--?降序之后取出前3條記錄 limit?3)union(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'數(shù)學(xué)' order?by?s.s_score?desc??? limit?3)union(select?s.s_id,?s.s_score,?c.c_name from?Score?s join?Course?c?on?s.c_id?=?c.c_id where?c.c_name?=?'英語' order?by?s.s_score?desc??? limit?3)

參考方法

通過Score表的自連接，表a中的值小于表b中的值，排序之后我們?nèi)∏?

selecta.s_id,a.c_id,a.s_score???--?取出a中的成績 from?Score?a join?Score?b on?a.c_id?=?b.c_id and?a.s_score?<=?b.s_score???--?表b中的成績大 group?by?1,2,3 having?count(b.s_id)?=?3 order?by?2,?3?desc;

我們通過語文這個(gè)科目來理解上面的代碼：前3名是80，80，76

--?語文 selecta.s_id,a.c_id,a.s_score???--?3、此時(shí)a表的成績就是我們找的?? from?Score?a join?Score?b on?a.c_id?=?b.c_id and?a.s_score?<=?b.s_score??--?1、判斷a的分?jǐn)?shù)小于等于b的分?jǐn)?shù)，要帶上等號(hào) and?a.c_id="01" group?by?1,2 having?count(b.s_id)?<=?3??--?2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對(duì)分?jǐn)?shù)相同的情形 order?by?3?desc;??? --?語文selecta.s_id,a.c_id,a.s_score???--?a表的成績 from?Score?a join?Score?b on?a.c_id?=?b.c_id and?a.s_score?<=?b.s_score???--?1、判斷a的分?jǐn)?shù)小于等于b的分?jǐn)?shù)，要帶上等號(hào) group?by?1,2,3 having?count(b.s_id)?<=?3???--?2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對(duì)分?jǐn)?shù)相同的情形 order?by?2,?3?desc;???--?課程（2）的升序，成績（）3的降序 推薦閱讀:入門:?最全的零基礎(chǔ)學(xué)Python的問題? |?零基礎(chǔ)學(xué)了8個(gè)月的Python??|?實(shí)戰(zhàn)項(xiàng)目?|學(xué)Python就是這條捷徑干貨:爬取豆瓣短評(píng)，電影《后來的我們》?|?38年NBA最佳球員分析?|? ?從萬眾期待到口碑撲街！唐探3令人失望? |?笑看新倚天屠龍記?|?燈謎答題王?｜用Python做個(gè)海量小姐姐素描圖?｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影趣味:彈球游戲? |?九宮格? |?漂亮的花?|?兩百行Python《天天酷跑》游戲!AI:?會(huì)做詩的機(jī)器人?|?給圖片上色?|?預(yù)測(cè)收入?|?碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影小工具:?Pdf轉(zhuǎn)Word，輕松搞定表格和水印！?|?一鍵把html網(wǎng)頁保存為pdf！|??再見PDF提取收費(fèi)！?|?用90行代碼打造最強(qiáng)PDF轉(zhuǎn)換器，word、PPT、excel、markdown、html一鍵轉(zhuǎn)換?|?制作一款釘釘?shù)蛢r(jià)機(jī)票提示器！?｜60行代碼做了一個(gè)語音壁紙切換器天天看小姐姐！｜年度爆款文案1).臥槽！Pdf轉(zhuǎn)Word用Python輕松搞定！2).學(xué)Python真香！我用100行代碼做了個(gè)網(wǎng)站，幫人PS旅行圖片，賺個(gè)雞腿吃3).首播過億，火爆全網(wǎng)，我分析了《乘風(fēng)破浪的姐姐》，發(fā)現(xiàn)了這些秘密?4).80行代碼！用Python做一個(gè)哆來A夢(mèng)分身?5).你必須掌握的20個(gè)python代碼，短小精悍，用處無窮?6).30個(gè)Python奇淫技巧集?7).我總結(jié)的80頁《菜鳥學(xué)Python精選干貨.pdf》,都是干貨?8).再見Python！我要學(xué)Go了！2500字深度分析！9).發(fā)現(xiàn)一個(gè)舔狗福利！這個(gè)Python爬蟲神器太爽了，自動(dòng)下載妹子圖片點(diǎn)閱讀原文,領(lǐng)AI全套資料！

總結(jié)

以上是生活随笔為你收集整理的MySQL经典50题目，必须拿下！的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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