use day5;  # 切換day5為當前資料庫show tables;Tables_in_day5coursescorestudentteacher

練習題

第十三題：查詢有缺考課程的學生學號、姓名和所有缺考課程：

這一題的難點在於怎麼找到所有學生的缺考資訊呢？資料庫中有所有學生的考試成績，但是缺考的情況沒有錄入學生id、課程id、得分num為0的記錄，那麼該怎樣找出缺考資訊呢？下面是我的解題步驟：1.將student表和course表做笛卡爾積，得出所有學號、姓名對應所有課程、課程名的記錄：

select sid,sname,cid,cname    from student,course

2.將第一步的表與score表做左連線，這個連線後的大表中num is NULL的記錄就是缺考的學號和課程記錄，將這些記錄篩選出來就能找到缺考課程的學生學號和課程：

select *    from (select sid,sname,cid,cname from student,course) a    left join score b    on  a.sid=b.student_id and a.cid=b.course_id    where num is NULL;

3.從上表中挑選sid,sname欄位，按sid分組，對課程名做group_concat：

select a.sid 學號,a.sname 姓名,group_concat(cname order by cid) 缺考課程    from(        select sid,sname,cid,cname            from student,course) a    left join score b    on  a.sid=b.student_id and a.cid=b.course_id    where num is NULL    group by a.sid;

小結：這一題比較難，考驗的是逆向思維能力。score表中很多學生缺考，直接查哪個學生缺考了哪門課程好像沒有方法。此時我們可以用學生表和課程表做笛卡爾積，那麼我們可以得到所有的學生對應了所有的課程全部記錄的大表，再將這張大表與score表左連線，此時篩選出左連線後表中num is NULL的記錄，那麼我們就找到了所有缺考的資訊。還要注意的細節有2條：一是where和group by可以同時使用，where字句必須放在前面。二是group_concat函式有兩個引數，引數一是欄位名，引數二是按指定欄位排序(預設升序，加desc改為倒序)。

第十四題：找出考試課程與“張宇”相同的學生學號和姓名：

這一題考核的重點是分組聚合、having篩選、group_concat。1.找出“張宇”考試的所有課程id(記得要排序)，將其儲存到變數cid中：

select @cid:=group_concat(course_id order by course_id)    from score    group by student_id    having student_id=(        select sid            from student            where sname='張宇');

2.把score表按學號分組並做分組過濾(過濾條件是所有課程id(記得要排序)與“張宇”相同：

select student_id    from score    group by student_id    having group_concat(course_id order by course_id)=@cid;

3.將上表查詢結果與student表連表，顯示學號和姓名，要記得排除“張宇”本身，最終完整程式碼如下：

select @cid:=group_concat(course_id order by course_id)    from score    group by student_id    having student_id=(        select sid            from student            where sname='張宇');select sid 學號,sname 姓名    from student a    right join(        select student_id            from score            group by student_id            having group_concat(course_id order by course_id)=@cid)b    on a.sid=b.student_id    where sname!='張宇';

第十五題：按所有課程平均成績從高到低全部學生的id、名字、課程數、平均分、各科成績：

1.這題要求是按所有課程平均分從高到低排序，實際上並不能算平均分，因為有些學生有些課程沒有成績，所以應該按score表中的總成績/課程數排序：

select student_id 學號,sum(num)/(select count(*) 課程數 from course) 平均分,count(num) 課程數    from score    group by student_id    order by 平均分 desc;

2.將上表與student表拼接，注意是student表的資料全保留：

select sid 學號,sname 姓名,課程數,平均分    from student a    left join(        select student_id 學號,sum(num)/(select count(*) 課程數 from course) 平均分,count(num) 課程數            from score            group by student_id) b    on a.sid=b.學號    order by 平均分 desc;

3.接下來要拼接各科成績和名字的表：

select c.student_id,group_concat(d.cname,':',num order by d.cid) 各課分數    from score c    inner join        course d    on c.course_id=d.cid    group by c.student_id;

4.再將第三步表和第四步表拼接起來，形成最終完整的表。另外這裡做一個最佳化，那個課程數可以提前算出來儲存在臨時變數中，避免重複運算，可以略微提高查詢效率：

select @課程數:=count(*)    from course;select e.學號,e.姓名,e.課程數,e.平均分,f.各課分數    from(        select sid 學號,sname 姓名,課程數,平均分            from student a        left join(            select student_id 學號,sum(num)/@課程數 平均分,count(num) 課程數                from score                group by student_id) b        on a.sid=b.學號) e    left join(        select c.student_id,group_concat(d.cname,'',num order by d.cid) 各課分數            from score c        inner join            course d        on c.course_id=d.cid        group by c.student_id) f    on e.學號=f.student_id    order by 平均分 desc;

總結

第十五題是所有練習題中最難的一題，在寫複雜查詢的時候要找到思路，按照查詢要求一步步地篩選出需要的資料，再一步步地拼接，最終實現輸出正確結果。

得出正確的結果以後，認真檢查程式碼找找有沒有邏輯缺陷、有沒有可提升查詢效率的最佳化空間和和輸出的資料格式是否美觀等細節。

就像第十五題中：先將課程數計算出來儲存到臨時變數中，那麼能避免重複的查course表的課程數，這樣可以略微提高查表效率；還有group_concat時要考慮到score表中的記錄是亂序的，所以為了顯示效果，那麼group_concat中再加上order by 課程id進行排序，最終輸出的內容比較美觀。

希望學習SQL的朋友不光要會查表，還要考慮效率和美觀。

select @課程數:=count(*)  # 從course表中統計總共有幾門課，將其報存到臨時變數“課程數”    from course;

寫註釋非常重要！若不寫註釋等過幾個月維護程式碼時自己看自己寫的程式碼都得花老長時間才懂，更別提其他人看你寫的程式碼了。記住，程式碼的可讀性非常重要！！！