• 分組統(tǒng)計(jì)求和，百分比
• 如何利用SQL實(shí)現(xiàn)排序
• having使用
• union拼接

5個(gè)題目是：

• 查詢不同老師所教不同課程平均分從高到低顯示
• 查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績
• 統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號，課程名稱，[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)化下

select
 s.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í)表t

on s.s_id = t.s_id

和第25題相同的方法

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

-- 語文
select
 a.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ù)，要帶上等號
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對分?jǐn)?shù)相同的情形
order by 3 desc
limit 1,2

-- 語文
select
 a.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ù)，要帶上等號
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對分?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ù)：課程編號，課程名稱，[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、如何對每個(gè)成績進(jìn)行分組展示：ABCD代表相應(yīng)的等級

select 
 c_id
 ,s_score
 ,case when s_score >= 85 and  s_score<= 100 then 'A'   -- 大小關(guān)系必須分兩次寫，一次寫的話MySQL無法識別
  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無法識別
  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、完整代碼

select
 s.c_id  編號
 ,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;  

注意對比：

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、我們對上面的結(jié)果進(jìn)行排序

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

-- MYSQL5.7中沒有rank函數(shù)，所以通過自連接實(shí)現(xiàn)

select
 t1.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_score
          from Score sc
          join Student s
          on sc.s_id=s.s_id
          group by sc.s_id,s.s_name)t2    -- 臨時(shí)表t2也是上面的結(jié)果
  where t2.avg_score >= t1.avg_score
  ) rank

from (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)t1   -- 臨時(shí)表t1就是上面的結(jié)果
order by t1.avg_score desc;

參考方法

select
 a.s_id  -- 學(xué)號
 ,@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_s
      from Score 
      group by s_id
      order by 2 desc)a    -- 表a：平均成績的排序和學(xué)號
      ,(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 t2
    where 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è)腳本：

1. 當(dāng)t1.s_score=89，滿足t2.s_score > = t1.s_score的有98，90和89，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是3
2. 當(dāng)t1.s_score=90，滿足t2.s_score > = t1.s_score的有98和90，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是2
3. 當(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
4. 當(dāng)t1.s_score=98，滿足t2.s_score > = t1.s_score的只有98，此時(shí)count(distinct t2.s_score) 的個(gè)數(shù)就是1
5. 當(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ù)對應(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)∏?

select
 a.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

-- 語文
select
 a.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ù)，要帶上等號
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3  -- 2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對分?jǐn)?shù)相同的情形
order by 3 desc;   

-- 語文

select
 a.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ù)，要帶上等號
group by 1,2,3
having count(b.s_id) <= 3   -- 2、b中的個(gè)數(shù)至少有3個(gè)，應(yīng)對分?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就是這條捷徑

干貨:爬取豆瓣短評，電影《后來的我們》 | 38年NBA最佳球員分析 |   從萬眾期待到口碑撲街！唐探3令人失望  | 笑看新倚天屠龍記 | 燈謎答題王 ｜用Python做個(gè)海量小姐姐素描圖 ｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

趣味:彈球游戲  | 九宮格  | 漂亮的花 | 兩百行Python《天天酷跑》游戲!

AI: 會做詩的機(jī)器人 | 給圖片上色 | 預(yù)測收入 | 碟中諜這么火，我用機(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夢分身 
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全套資料！