滴滴2020年面試題：如何找出最小的N個數？

【題目】

“學生表”裡記錄了學生的學号、入學時間等資訊。“成績表”裡是學生選課成績的資訊。兩個表中的學号一一對應。（滴滴2020年面試題）

現在需要：

1. 篩選出2017年入學的“計算機”專業年齡最小的3位同學名單（姓名、年齡）
2. 統計每個班同學各科成績平均分大于80分的人數和人數占比

【解題思路】

問題1：篩選出2017年入學的“計算機”專業年齡最小的3位同學名單（姓名、年齡）

一看是不是有點懵？

别着急，我們用邏輯樹分析方法，把這個複雜問題拆解為一個一個可以解決的簡單問題：

1）篩選條件：入學時間是2017，專業是計算機

2）最小的3位同學名單（姓名、年齡）

1.先找出符合要求的同學

篩選條件：入學時間是2017，專業是計算機。year(日期)函數用來擷取日期的年份

​

select 姓名,年齡

from 學生表

where 專業='計算機' and year(入學時間)=2017;

2.最小的3位同學名單（姓名、年齡）

先使用order by對年齡排序（從小到大，也就是升序asc），然後使用limit輸出前3行資料，就是年齡最小的3位。

where 專業='計算機' and year(入學時間)=2017

order by 年齡 asc

limit 3;

問題2：統計每個班同學各科成績平均分大于80分的人數和人數占比

（1）每位同學的平均成績

（2）平均分大于80分的人數

（3）平均分大于80分的人數占比

（4）輸出結果是班級，平均分大于80分的人數，平均分大于80分的人數占比

1. 每位同學的平均成績

涉及到“每個”的時候，就要想到《猴子 從零學會sql》裡的分組彙總了。按學号分組（group by），然後求平均成績（avg函數），把所得結果看做臨時表。

select 學号,avg(分數) as 平均成績

from 成績表

group by 學号;

2.平均成績>80的人數

可以使用使用sum函數和case表達式來統計平均成績大于80的人數

select sum(

case when 平均成績>80 then 1

else 0            

end) as 人數

from 臨時表；

下圖是case和sum結合起來統計人數的sql過程：

3.平均成績大于80分的人數占比

平均成績>80的人數占比 =（平均成績>80的人數）/ 總人數

總人數是表行數：count(學号)。是以平均成績>80的人數占比就是：

else 0            

end)/count(學号) as 人數占比

1. 輸出結果是班級、人數、人數占比

班級在“學生表”中，這涉及到需要将“學生表”和“臨時表”2張表，需要用到多表聯結。聯結兩表的是“學号”，如下：

因為要保留“學生表”班級的全部資料，是以使用左聯結。

select a.班級

from 學生表 as a

left join 臨時表 as b

on a.學号=b.學号

group by 班級;

題目要求是輸出班級、人數、人數占比，是以在上面sql中加入輸出的列名：

select a.班級,人數,人數占比

select子句中的人數、人數占比在前面第1步、第2步中已經得到，套入這個sql語句中就是：

最終sql如下：

select a.班級,

sum(

case when b.平均成績>80 then 1

else 0 end) as 人數,

else 0 end)/count(a.學号) as 人數占比

from 學生表 as a left join(

group by 學号

) as b

group by 班級

【本題考點】

1.使用邏輯樹分析方法将複雜問題變成簡單問題的能力

2.當遇到“每個”問題的時候，要想到用分組彙總

3.查詢最小n個資料的問題：先排序（order by），然後使用limit取出前n行資料

4.遇到有篩選條件的統計數量問題時，使用case表達式篩選出符合條件的行為1，否則為0。然後用彙總函數（sum）對case表達式輸出列求和。

有篩選條件的統計數量問題的萬能模闆

case when <判斷表達式> then 1

else 0           

end

) as 數量

from 資訊表；

【舉一反三】

1.查詢最小/最大的N個資料的問題

某網站有購買記錄表，找出消費最大的2名顧客，輸出顧客ID和消費金額

select 顧客ID,消費金額

from 購買記錄表

order by 消費金額 desc

limit 2;

1. 分組彙總問題

某網站有顧客表和消費表，請統計每個城市的顧客平均消費在1000元以上的人數，輸出城市，人數

select a.城市,

case when b.平均消費>1000 then 1

else 0 end) as 人數

from 顧客表 as a left join(

select ID,avg(消費金額) as 平均消費

from 消費表

group by ID

on a.ID=b.ID

group by 城市;

推薦：如何從零學會sql？