sql 經典查詢50題 思路（一）
- 2019 年 11 月 3 日
- 筆記
因為需要提高一下sql的查詢能力，當然最快的方式就是做一些實際的題目了。選擇了這個sql的50題，這次大概做了前10題左右，把思路放上來，也是一個總結。
具體題目見:
https://zhuanlan.zhihu.com/p/72223558
第一部分的題目主要使用的技術是連表查詢和子查詢，難倒不難，主要是要把思路轉換過來。
首先畫出一個各個表之間的關係圖（沒畫圖之前關係老是搞不清）
1.查詢” 01 “課程比” 02 “課程成績高的學生的信息及課程分數
學生的信息在表1當中，課程成績在表4當中，當然要用到連表查詢。
這裡很有普遍性的一個問題是：要從表4中找出Sid相同，但是Cid為1的score大於Cid為2的score的記錄。這裡要使用子查詢，分別限定條件為Cid=‘1’，Cid=’2′,變成兩個表，再查滿足條件的就很簡單了。
select Student.SId,Student.Sname,Student.Sage,Student.Ssex,r.科目一成績,r.科目二成績 from study.dbo.Student right join (select t1.SId as 學生ID,t1.score as 科目一成績,t2.score as 科目二成績 from (select SId,score from study.dbo.SC where CId='01')as t1, (select SId,score from study.dbo.SC where CId='02')as t2 where t1.SId=t2.SId and t1.score>t2.score) as r on Student.SId=r.學生ID
join — on這個也是常用的思路，當要連接兩個某一列相關的表時。
1.1查詢存在” 01 “課程但可能不存在” 02 “課程的情況(不存在時顯示為 null )
和第一題思路類似，注意以01課程為準，所以要用left join
select * from (select SId,score from study.dbo.SC where CId='01') as t1 left join (select SId,score from study.dbo.SC where CId='02') as t2 on t1.SId=t2.SId
1.2 查詢同時存在01和02課程的情況
很簡單，用inner join，求兩表交集
select t1.SId,t1.score,t2.score from (select SId,score from study.dbo.SC where CId='01') as t1 inner join (select SId,score from study.dbo.SC where CId='02') as t2 on t1.SId=t2.SId
1.3查詢選擇了02課程但沒有01課程的情況
我的思路是還是用一個right join，然後判斷NULL值，不知道會不會比not in效率高。
select t2.SId,t2.score from (select SId,score from study.dbo.SC where CId='01') as t1 right join (select SId,score from study.dbo.SC where CId='02') as t2 on t1.SId=t2.SId where t1.score is null
2.查詢平均成績大於等於 60 分的同學的學生編號和學生姓名和平均成績
肯定要連表，有表一有表四。平均成績涉及到group by，對平均成績的限制涉及到having語句
select t1.SId,t1.avg_score,t2.Sname from ( select SId,AVG(score) as avg_score from study.dbo.SC group by SId having AVG(score)>60 ) as t1 inner join study.dbo.Student as t2 on t1.SId=t2.SId
3.查詢在 SC 表存在成績的學生信息
依然是連表查詢，表一的sid等於表四的sid，去除重複值使用DISTINCT即可
select DISTINCT Student.SId,Student.Sname,Student.Sage,Student.Ssex from study.dbo.SC inner join Student on SC.SId=Student.SId
4.查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null )
依然是連表查詢，left join
select Student.*,t2.count_id,t2.avg_score from Student left join (select SId,count(CId) as count_id ,avg(score)as avg_score from study.dbo.SC group by SId) as t2 on Student.SId=t2.SId
4.1 查有成績的學生信息
inner join，不贅述
select Student.*,t2.count_id,t2.avg_score from Student inner join (select SId,count(CId) as count_id ,avg(score)as avg_score from study.dbo.SC group by SId) as t2 on Student.SId=t2.SId
5.查詢「李」姓老師的數量
最簡單的一題，知道like這種模糊查詢就行
select COUNT(*) from Teacher where Tname like '李%'
6.查詢學過「張三」老師授課的同學的信息
這個有意思，代表着從一張表跳到另一張表找信息
第一個思路當然是用join，多個表一個一個on連接起來
select Student.* from (select tid from Teacher where Tname='張三') as t1 inner join Course on t1.tid=Course.TId inner join SC on Course.CId=SC.CId inner join Student on SC.SId=Student.SId
但是也有另一種寫法
select study.dbo.Student.* from teacher,study.dbo.Course ,study.dbo.student,study.dbo.sc where teacher.Tname='張三' and teacher.TId=Course.TId and Course.CId=sc.CId and sc.SId=student.SId
直接from多個表，在where里寫=
我查了一下，其實這種方式是用了隱式的inner join，效率差異不大
7.查詢沒有學全所有課程的同學的信息
查到沒有學全所有課程同學的sid很簡單，在表4中查詢。同學的信息用inner join聯表1查詢實現。
SELECT * FROM study.dbo.Student as t1 inner join (select Student.SId from Student left join study.dbo.SC on Student.SId=SC.SId group by Student.SId having COUNT(SC.CId)!=(select count(*) from study.dbo.Course)) as t2 on t1.SId=t2.SId
