前言 

                                                                                                                                   

前期我們大概提到了撰寫 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指令本來也就不該寫的太過於複雜，這些範例大多都是考試的例子，或是一些實務上有人問到的例子，之前還會有類似的範例，各位可以先看問題，先自已想一下答案該如何做，最後再自行測試結果。