讓我們每天都進步一點點
題目快速查找索引
閱讀指南
上篇：SQL面試50題——思路解答與分類整理(上)聚合函數與表連接配接
- 【第一部分】聚合函數(sum/avg/count/min/max)
- 【第二部分】表連接配接(inner join/left join/right join/full join/exclude join)
中篇
- 【第三部分】視窗函數+Limit+變量(rank/dense rank/row number)
- 【第四部分】子查詢
下篇
- 【第五部分】CASE
- 【第六部分】日期函數
【第三部分】視窗函數+Limit+變量
知識要點與易錯點總結 在MySQL中，Limit用于指定要傳回的記錄數量
- Limit有兩個參數，前一個數字是開始行号，後一個數字是限制條數
- 例1：Limit 2,1 指的是從第2行開始，再多選擇1行，也就是說隻選了第3行
- 例2：Limit 3 指的是選擇前3條記錄，也就是說省略了第二個參數
因為SQL不能Group by以後取Limit，是以為了進行每組内排名，就需要用到視窗函數了，常見使用場景例如：
- 排名問題：顯示各科成績的排名
- TopN問題：按照總成績進行排名，并獎勵班級前三名
視窗函數的文法規則如下： over (partition by order by )
- 的位置，可以寫以下兩種函數：
- 1. 視窗函數，包括rank, dense_rank, row_number等
- 2. 聚合函數，如sum, avg, count, max, min等
- 解釋一下幾個主要的視窗函數：
- rank() 是跳躍排序，兩個并列第二名下來就是第四名，即1,2,2,4,5
- dense_rank() 是連續排序，兩個并列第二名後仍然跟着第三名，即1,2,2,3,4
- row_number() 是沒有重複值的排序，可以利用它來實作分頁，永遠是1,2,3,4,5(即使原本的資料有重複值)
- 注意：因為視窗函數是對where或者group by子句處理後的結果進行操作，是以視窗函數原則上隻能寫在select子句中
視窗函數題目整理(完整題目清單見文章最後) 【18. 查詢各科成績前三名的記錄】
- 查詢各科成績前三名，是典型TopN問題
- 視窗函數隻能用在select後面，不能用在where後面，是以要先寫子查詢，并且子查詢産生的表需要有命名，例如t1，否則會報錯
- rank() 函數保留名次空缺，即有重複名次時會跳過，兩個第二名并列時顯示1,2,2,4,5
- dense_rank() 函數不保留名次空缺，兩個第二名并列時顯示1,2,2,3,4
- Partition by後用課程cid分組，Order by後用分數cscore排名
【15.1 按各科成績進行排序，并顯示排名， 成績重複時保留名次空缺】***相比上一題【18. 查詢各科成績前三名的記錄】，這裡不但需要顯示排名，還需要顯示各科成績，是以用視窗函數顯示排名是最簡單的方法解法1：視窗函數
- 保留名次空缺，使用rank函數，以課程編号cid分組，按分數cscore排名
- rank函數在有重複值時使用跳躍排序，即有并列第二時，顯示1,2,2,4,5
解法2：表連接配接查詢如果不用rank函數，寫法就相對複雜一點了，下面是思路分析第一步：初步排名
- 為了對每個學生的各科成績排序，可以把成績表自交，用課程編号cid來連接配接兩個score表(因為是對比課程的成績)
- join條件是當cid相同的時候，左表成績
- 最後注意Order by的條件，之是以先按sid排序，後按cid排序，是為了能看到同一個學生的每一節課的成績排行
- 結果為sid=01号學生，他的1/2/3三門課的成績在右表都是null，也就是說沒有其他人的成績比他高，學霸實錘
- 結果為sid=02号學生，對于cid 01課程，右表有01/05/03三個同學的cid 01課成績比他高；對于cid 02課程，右表有05/01/03/07四個同學的cid 02課成績比他高；以此類推
初步排名 第二步：加入學生名次
- 對于sid=01号學生，右表資料都是null，是以對于第一行到第三行，count(t2.cscore)都是null，是以排名為 count(t2.cscore)+1 = 1，即第一名
- 對于sid=02号學生，cid=01課程的右表有三行，即三個同學成績比他高，是以sid 02号的排名為 count(t2.cscore)+1 = 3+1 = 4，即第四名
- 是以把count(t2.cscore)+1作為新字段rank進行排名即可，同時按t1.cid, t1.sid, t1.cscore對結果分組，最後按cid排序
- 補充：其實count(t2.sid)+1或count(t2.cid)+1都可以，反正右表有一行資料就count一次
count計算排名
最終結果 【15.2 按各科成績進行排序，并顯示排名， 成績重複時合并名次】***和上一題的差別就是如果有重複成績，合并名次，即有并列第二時，顯示1,2,2,3,4解法1：視窗函數
- 不保留名次空缺，使用dense_rank函數，以課程編号cid分組，按分數cscore排名
- rank函數在有重複值時使用跳躍排序，即有并列第二時，顯示1,2,2,4,5
解法2：表連接配接
- 基本和上一題的代碼一緻，但是這次count函數必須要使用t2.cscore了，因為需要找到distinct的cscore然後進行排名，這樣的話如果有并列的分數，cscore隻會被count一次
【16. 查詢學生的總成績，并進行排名，總分重複時保留名次空缺】解法1：視窗函數
- 省略partition by子句就是不指定分組，隻指定按總分排序
解法2：使用變量 變量就是一個容器，在需要不斷更改的地方放一個變量，而不是固定的值，這樣每次需要更改的時候，隻需要更改變量的值就可以。這一題解法2中使用變量myrank儲存排名的動态變化。說明一下變量的使用方法：
- MySQL裡面的變量是不嚴格限制資料類型的，它的資料類型根據你賦給它的值而随時變化
- 定義變量
- 使用 @變量名 來定義一個變量，如 @abc，就是建立一個叫做abc的變量
- 給變量指派
- 使用set時可以用“=”或“:=”，但是使用select時必須用“:=指派”
- set @abc=1 或set @abc:=1，就是使用set語句建立并初始化變量abc的值為1
- select @abc:=1，就是把1指派給變量abc
- select @abc:=字段名 from 表名 where ...，就是從資料表中擷取一條記錄字段的值給變量abc，在執行後輸出到查詢結果集上面
解法2：使用變量方法1(設定變量myrank初始值)
- set語句用來建立并初始化變量
- set @myrank = 0; 這行代碼每次都需要運作，将變量初始值設為0
- 接着在select語句中設定變量 @myrank := @myrank+1 as 排名
解法2：使用變量方法2(每次查詢都初始化變量myrank)
- 使用子查詢，(select @myrank := 0) as t2，然後從t1和t2兩個表查詢内容
【36. 查詢所有課程成績第二名到第三名的學生資訊及課程成績】
- 第一步，使用子查詢通過dense_rank函數查詢排名情況，并且将查詢結果存為新表t1
- 第二步，通過學生編号sid連接配接學生表和新表t1
- 第三步，加入Where條件篩選，查找排名為第二名和第三名的資料，(2,3) 代表排名=2 or 排名=3，不是一個區間範圍
- 最後用兩個條件進行排序，先用cid排序(按各科成績排序)，再用排名排序(按各科成績排名排序)，排序規則預設是asc
【第四部分】子查詢
知識要點與易錯點總結什麼情況下需要用子查詢？
- 用某個查詢結果作為另一個查詢的條件的時候
- 不能直接join，需要先統計出中間資料的時候
- 多表聯合查詢的時候或者是檢索指定的資料的時候
比較運算符
- IN：在範圍内的值，隻要有就是True
- 隻用于子查詢：ANY(和子查詢傳回的任何一個值比較為True，則傳回True)
- 隻用于子查詢：ALL(和子查詢傳回的所有值比較為True，則傳回True)
子查詢題目整理(完整題目清單見文章最後) 【3. 查詢在成績表存在成績的學生資訊】 【28. 查詢所有學生的課程及分數情況(存在學生沒成績，沒選課的情況)】***兩題思路一緻，可以用子查詢，也可以用表連接配接，第28題和第3題解法2的差別就是不加Where篩選成績非null值的資料(存在學生沒成績，沒選課的情況)解法1：子查詢
- 第一步，用子查詢，找到成績表中有成績的學生sid
- 第二步，拿這個sid去學生表查學生資訊
解法2：表連接配接
- Left join 學生表和成績表，然後用Where過濾聯合表裡cscore不為null的學生(有cscore就有成績)
- 然後使用select distinct語句來去重，每個學生的資訊隻傳回一行
【6. 查詢學過"張三"老師授課的同學的資訊】***可以用Where直接多表查詢，也可以用子查詢，子查詢的思路與下一題【10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名】相同 【10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名】
- 總的思路是反向思考，即先找到所有上過張三老師課的學生，取反即為沒上過課的學生
- 寫SQL子查詢的思路，越先查的東西其實是放在越後面的
- 第一步，line4，在teacher表中查到張三老師的tid
- 第二步，line3，用第一步找到的tid，在course表中查到這個tid對應的cid，即這個老師教的課是哪一門
- 第三步，line2，用第二步找到的cid，在score表中查到學過這門課的學生sid
- 第四步，line1，用第二步找到的sid，在student表中查找對應學生，反向思考，沒上過這門課就用not in
【33. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及成績】
- 多表聯合查詢，直接用Where多條件篩選最快
- 解法1使用Max函數，隻傳回成績最高的第一條記錄
- 解法2用cscore從高到低排序後，使用LIMIT關鍵字傳回第一條記錄
【34. 成績有重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及成績】***和上一題的差別是，如果張三老師所授課程裡有多個學生獲得并列第一的成績，需要全部找出來，這時用MAX隻能找到其中一個，并且如果不知道到底有幾個學生是并列第一的話，也沒有辦法使用LIMIT。比較好的方式是子查詢。第一步：修改成績表
- 因為原本的成績表裡02課程沒有兩個相同的最高分，是以可以先手動修改一下資料表，把07号學生的02課成績從89改成90分，這樣成績表的02課程就有兩個90分
- 如果MySQL報錯，是因為安全模式的限制，需要點選頂部菜單欄的Edit → Preference → SQL Editor → 取消勾選Safe Updates → 重新開機MySQL
- 建議運作完Update語句後，把Safe Updates重新勾選回來
第二步：查詢多個02課程最高分
- 與33題的差別就是加了個子查詢，隻用來查找最高分，然後用Where找到成績符合最高分的所有學生，結果如下圖所示
- 建議這題做完之後重新運作Update語句把07号學生的02課成績改回89分
【7. 查詢沒有學全所有課程的同學的資訊】 【39. 查詢選修了全部課程的學生資訊】***這裡主要解釋第7題，第39題的代碼與本題解法1裡的子查詢代碼一緻解法1：反向思考
- 思路是反向思考，先找到上了所有課程的學生，然後排除這些學生，就是沒有學全所有課程的學生了，用not in關鍵字
- 如何找學全所有課程的學生？關鍵在于score表裡的每個學生要同時有3個cid(01/02/03)，是以思路是對student表做如下過濾：
- 第一步，從course表中查找一共有幾個cid (目前是3)，是以select count(course.cid) from course
- 第二步，從score表中查找符合第一步中查到的cid count數量 (目前是3) 的學生sid，是以having count(score.cid) = 第一步的結果
- 第三步，從student表中查找第二步中查到的sid的學生資訊，因為是取反，是以用not in
解法2：表連接配接 解法2：表連接配接方法1
- 第一步，從course表中查找一共有幾個cid (目前是3) ，是以是select count(course.cid) from course
- 第二步，把student表和score表join起來，然後按照student.sid或score.sid分組
- 第三步，查找聯合表中同一個sid對應的cid count數量 (目前為3) 的情況，即having子句代表某個sid學的課程數量不等于course表裡一共的課程數量
解法2：表連接配接方法2
- 與方法1相似，但用inner join + where連接配接表
- inner join從查詢意義上來講，用where或on的效果相同，文法也不會報錯
- 但是這題用left join更合理，因為如果一個學生要是一門課程都沒學的話，inner join後就不會顯示這個學生了
【8. 查詢至少有一門課與學号為"01"的同學所學相同的同學的資訊】
- SQL的多表查詢是越後面的越先查詢，是以要最先找的表要放在最後面，是以：
- 第一步，從score表查詢sid=01的學生的所有cid
- 第二步，根據第一步找到的score表cid，去score表查詢有這些cid課的學生sid，同時sid不為01(排除掉sid=01學生)
- 第三步，根據第二步找到的score表sid，去student表查詢第二步找到的所有sid的學生資訊
【9. 查詢和"01"号的同學學習的課程完全相同的其他同學的資訊】***這題是上一題【8. 查詢至少有一門課與學号為"01"的同學所學相同的同學的資訊】的難度提高版，而且網上能查到的答案大多是僞答案，比如說在第8題的基礎上再加一個條件找出選課數也和01同學相同的學生，這種解法其實沒有真正解決問題。經過研究發現，group_concat()函數可以解決這個題目的要求，文法規則如下： group_concat( [distinct] 需要連接配接的字段 [order by 需要排序的字段 asc/desc] [separator '分隔符'] )
- group_concat() 将Group by産生的同一個分組中的值連接配接起來，傳回一個字元串結果
- [ ]方括号裡的内容都是可選參數，可以不寫
- 使用distinct可以排除重複值，需要連接配接的字段可以是多個，需要排序的字段也可以是多個
- separator後面需要一個字元串值
補充：concat_ws() 函數也有把兩個字元串連接配接為一個字元串的效果，但是無法針對分組做處理，另外concat_ws() 函數文法為： concat_ws('分隔符', str1, str2, ...)第一步：找到所有學生的選課情況
- Group by以學生編号sid分組，也就是說要對分組後每個學生sid對應的所有cid進行連接配接
- group_concat的參數裡，需要連接配接的字段為cid，按cid排序，分隔符為 '-'
所有學生的選課情況 第二步：找到01号學生的選課情況
- 與第一步的差別就是加了Where篩選sid=01的情況，這時01學生的選課情況字段傳回結果應該是：01-02-03
第三步：join上面的兩個表，連接配接條件為 選課情況 字段，并去除01号同學的記錄
- t1表是所有學生的選課情況，t2表是01号學生的選課情況，兩個表join後，再與學生表join，查詢學生資訊，并删掉sid=01的學生資訊
最終結果 【35. 查詢不同課程成績相同的學生的學生編号、課程編号、學生成績】***題目本身有歧義，這裡了解為找出選了不止一門課，且自己的不同課程分數相同的學生，比如說一個學生選了01課和03課，且分數都是80解法1：子查詢+自交
- 原樣克隆一遍成績表，然後查詢t1和t2中課程編号cid不同但分數cscore相同的學生編号sid
解法2：子查詢+聚合函數
- 查出選課數大于1的學生，并且不同課程分數的最大值=最小值(即分數相等)
附錄
題目清單： 1.1 查詢"01"課程比"02"課程成績高的學生的資訊及課程分數 1.2 查詢同時上過"01"課程和"02"課程的學生 1.3 查詢上過"01"課程但可能沒上過"02"課程的學生 (這種情況顯示為 null) 1.4 查詢沒上過"01"課程，隻上過"02"課程的學生 2. 查詢平均成績大于等于 60 分的同學的學生編号、學生姓名和平均成績 3. 查詢在 SC 表存在成績的學生資訊 4. 查詢所有同學的學生編号、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null) 5. 查詢「李」姓老師的數量 6. 查詢學過張三老師授課的同學的資訊 7. 查詢沒有學全所有課程的同學的資訊 8. 查詢至少有一門課與學号為"01"的同學所學相同的同學的資訊 9. 查詢和"01"号的同學學習的課程完全相同的其他同學的資訊 10. 查詢沒學過"張三"老師講授的任一門課程的學生姓名 11. 查詢兩門及其以上不及格課程的同學的學号，姓名及其平均成績 12. 檢索" 01 "課程分數小于 60，按分數降序排列的學生資訊 13. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績 14. 查詢各科成績最高分、最低分和平均分，以如下形式顯示： 課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率 (及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90) 要求輸出課程号和選修人數，查詢結果按人數降序排列列，若人數相同，按課程号升序排列 15.1 按各科成績進行排序，并顯示排名， 成績重複時保留名次空缺 15.2 按各科成績進行排序，并顯示排名， 成績重複時合并名次 16. 查詢學生的總成績，并進行排名，總分重複時保留名次空缺 17. 統計各科成績各分數段人數：課程編号，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比 18. 查詢各科成績前三名的記錄 19. 查詢每門課程被選修的學生數 20. 查詢出隻選修兩門課程的學生學号和姓名 21. 查詢男生、女生人數 22. 查詢名字中含有「風」字的學生資訊 23. 查詢同名同性學生名單，并統計同名人數 24. 查詢 1990 年年出生的學生名單 25. 查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編号升序排列 26. 查詢平均成績大于等于 85 的所有學生的學号、姓名和平均成績 27. 查詢課程名稱為「數學」，且分數低于 60 的學生姓名和分數 28. 查詢所有學生的課程及分數情況(存在學生沒成績，沒選課的情況) 29. 查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數 30. 查詢不及格的課程 31. 查詢課程編号為 01 且課程成績在 80 分及以上的學生的學号和姓名 32. 求每門課程的學生人數 33. 成績不重複，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績 34. 成績有重複的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生資訊及其成績 35. 查詢不同課程成績相同的學生的學生編号、課程編号、學生成績 36. 查詢所有課程成績第二名到第三名的學生資訊及課程成績 37. 統計每門課程的學生選修人數(超過 5 人的課程才統計) 38. 檢索至少選修兩門課程的學生學号 39. 查詢選修了全部課程的學生資訊 40. 查詢各學生的年齡，隻按年份來算 41. 按照出生日期來算，如果目前月日 < 出生年月的月日，年齡減一 42. 查詢本周過生日的學生 43. 查詢下周過生日的學生 44. 查詢本月過生日的學生 45. 查詢下月過生日的學生
作者：胖丁職場生存筆記
讓我們每天都進步一點點
Follow Me
轉一轉
贊一贊
看一看