前言
前期我們大概提到了撰寫 SQL指令的基本概念,其實就是一個
生產線的流程,來源的資料,要經過多少流程的轉換,才會得到
你所想看的結果。也就是如下圖:
圖一:T-SQL指令撰寫的流程。

圖一:T-SQL指令撰寫的流程。
而從本期開始,將會用一次一個實例的方式,來介紹這樣子的一個概念。
點歌系統
這個問題是某年高考的資料庫考題,原本是個題組,主要是請考生設計點歌系統的資料庫,第二段才是問 SQL指令。而點歌系統不外乎是歌曲、會員還有所點的歌,所以第一段題組算是相當的簡單,這裡就由我們來公佈設計出的結果,而至於第二段的題組則是要問 SQL指令,就可以請各位當作一個練習,來測驗各位是否具備有 SQL指令撰寫的能力。
設計出來的資料庫
我們在此把點歌系統的資料庫設計出來如下:
Member資料表:包含 MID、Password以及Mname,也就是會員編號(主鍵)、密碼與會員姓名資料。
Song資料表:包含 SID、SName,也就是歌曲編號(主鍵)以及歌曲的名稱。
MemberSong資料表:包含 SID、MID,也就是此資料表為 Member與 Song資料表中多對多所產生的第三實體資料表。
想看到的結果
有了資料來源,接下來就是你想要看什麼資料。高考資料庫的考題是這麼問的:請列出每個會員「必點」的歌曲。
我們可以試著塞入一些假造的資料,這樣會比較好理解,也就是輸入下列的 SQL指令把資料產生出來,看著來源,看著結果,再想如何把資料查詢出來。
Member(MID,Password,MName)
create table Member (MID int not null primary key , MName varchar(10))
insert Member values (1,'John')
insert Member values (2,'Mary')
insert Member values (3,'Tom')
Song (SID,SName)
create table Song(SID int not null primary key , SName varchar(10))
insert Song values (1,'星星')
insert Song values (2,'月亮')
insert Song values (3,'太陽')
MemberSong(MID,SID)
create table MemberSong(MID int , SID int)
insert MemberSong values (1,1),(1,2),(1,3),(2,1),(3,1)
也就是,現在有三首歌,分別為星星、月亮以及太陽,而會員有三名,分別是:John、Mary以及 Tom。
用肉眼可以看的出來,星星這首歌三個人都有點,但這是因為資料少,你可以用肉眼看,但實際上我們還是得用語法得到哪首歌為會員「必點」的歌。
如果這個問題,只要得到必點的歌曲編號,可能就單純很多,但此例是需要得到必點的「歌名」,李組長眉頭一皺,表示問題並不單純。因為如果只要歌曲編號,難度就低蠻多的,但若要的還包含歌名,可能又增加了撰寫程式的難度。
整理一下,目前我們的資料來源如下:
Member
1 John
2 Mary
3 Tom
Song
1 星星
2 月亮
3 太陽
MemberSong
1 1
1 1
1 3
2 1
3 1
而我們希望得到的結果:
結果
SID SName
1 星星
如何把來源的三個資料表,變成只有兩個欄位的結果集呢?首先我們先了解一下「會員必點的歌曲」這句話:
會員必點,代表會有會員點歌資料表資訊會被加入。如果是必點,點表會員有三個,A歌曲就要被點三次才叫必點,所以會有運算式計算總會員數,亦即要用 having count(MemberSong.SID)=會員個數這個運算式。
由於 MemberSong是第三實體,MID與 SID兩個 KEY所產生的資料列是不重複的,所以可以不用擔心多點歌的問題,就算會員點了同一首歌10首,在資料表中還是只會記錄一筆。
會員個數不可能給定值,所以這樣要寫次查詢,換句話說,句子應該是長的如下:
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member)
由於是查詢個數,個數的計算是要到 SELECT句法的後期才會算出,所以要用 having,而既然使用 having,就必需要用 group by,依歌曲分組加總,所以 T-SQL指令變成如下:
Select Song.SID,COUNT(MemberSong.SID) as count from Song inner join
MemberSong on Song.SID = MemberSong.SID
group by Song.SID
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member)
這樣則可以取出歌曲的點選次數是等於會員個數的。
不過, group by的限制在於,它只能 group by非彙總函數欄位,沒有要彙總的不能列在非彙總列表,所以不能寫成:
Select Song.SID,Song.SName,COUNT(MemberSong.SID) as count from Song inner join
MemberSong on Song.SID = MemberSong.SID
group by Song.SID
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member)
這樣勢必 group by 後面要接
group by Song.SID,Song.SName
所以為了把歌名顯示出來,我們使用次衍生資料表的查詢把最後的工作完成,結果就變成如下:
select Song.SID,Song.SName from Song inner join
(Select Song.SID,COUNT(MemberSong.SID) as count from Song inner join
MemberSong on Song.SID = MemberSong.SID
group by Song.SID
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member))
as a
on Song.SID = a.SID
所以這個句子的生產線流程順序是:
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member) à代出要使用 group by句法à Select Song.SID,Song.SName,COUNT(MemberSong.SID) as count from Song inner join
MemberSong on Song.SID = MemberSong.SID
group by Song.SID
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member)à為了找出歌名,所以最後要再產生一個衍生資料表
select Song.SID,Song.SName from Song inner join
(Select Song.SID,COUNT(MemberSong.SID) as count from Song inner join
MemberSong on Song.SID = MemberSong.SID
group by Song.SID
having COUNT(MemberSong.SID)=(Select COUNT(*) from Member))
as a
on Song.SID = a.SID
經過這些流程後,最後的結果也產生出來了!
結語
本期大概介紹我們前期所提及的寫 SQL指令的生產線流程,這次的範例還算是個簡單的例子,所使用的流程比較少而簡單,不過平常的 SQL指令本來也就不該寫的太過於複雜,這些範例大多都是考試的例子,或是一些實務上有人問到的例子,之前還會有類似的範例,各位可以先看問題,先自已想一下答案該如何做,最後再自行測試結果。
