這樣的 SQL如何寫 (二)

by adonis 23. 四月 2018 20:29

前言 

                                                                                                                                   

前期我們大概提到了撰寫 SQL指令的基本概念,其實就是一個 

生產線的流程,來源的資料,要經過多少流程的轉換,才會得到 

你所想看的結果。也就是如下圖:

圖一:T-SQL指令撰寫的流程。

 


 

圖一:T-SQL指令撰寫的流程。 

 

而從本期開始,將會用一次一個實例的方式,來介紹這樣子的一個概念。 

點歌系統 

這個問題是某年高考的資料庫考題,原本是個題組,主要是請考生設計點歌系統的資料庫,第二段才是問 SQL指令。而點歌系統不外乎是歌曲、會員還有所點的歌,所以第一段題組算是相當的簡單,這裡就由我們來公佈設計出的結果,而至於第二段的題組則是要問 SQL指令,就可以請各位當作一個練習,來測驗各位是否具備有 SQL指令撰寫的能力。 

設計出來的資料庫 

我們在此把點歌系統的資料庫設計出來如下: 

 

Member資料表:包含 MIDPassword以及Mname,也就是會員編號(主鍵)、密碼與會員姓名資料。

 

Song資料表:包含 SIDSName,也就是歌曲編號(主鍵)以及歌曲的名稱。

 

MemberSong資料表:包含 SIDMID,也就是此資料表為 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)

 

也就是,現在有三首歌,分別為星星、月亮以及太陽,而會員有三名,分別是:JohnMary以及 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指令本來也就不該寫的太過於複雜,這些範例大多都是考試的例子,或是一些實務上有人問到的例子,之前還會有類似的範例,各位可以先看問題,先自已想一下答案該如何做,最後再自行測試結果

 


 

Tags:

SQL Server資料庫 | 楊先民Adonis Young

不允許評論

NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List