這樣的 SQL如何寫 (三)

by adonis 17. 二月 2012 11:07

作    者:楊先民
審    稿:陳俊宇

前言
   
前期我們大概介紹了點歌的 T-SQL程式,也順道帶到了在第一
期所提到的生產線流程,所以我們在每一期開始,都要再複習一
下這個生產線流程,也就是如下圖所示:

image

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

而上一期的點歌系統,有來源與想看的結果,只是看中間如何拚湊出轉換的指令。

借書系統


這個問題也是某年高考的資料庫考題,請考生分析資料庫,並且利用 SQL指令取得原題意想要的結果,本題是一個借書系統,請看下面的資料庫設計,以及問題。不過在看這個資料庫之前,其實你心裡應該大概也有個底,知道借書系統可能會有哪些原素在裡面,例如借書人的資訊,借了哪些書,以及書的資料等等,然後你想要從這些資料庫之中如何撈出資料。
設計出來的資料庫
我們在此把借書系統的資料庫設計出來如下:

Book資料表:包含 callNo、isbn以及title、subject,也就是書的編號(主鍵)、isbn碼、書名與主題的資訊,這裡不是以 isbn碼當成主索引鍵而是採用 callNo當成一個替代鍵。

Patron資料表:包含 pId、name與startDate,也就是讀者資料中的編號、姓名與加入會員日,其中 pId為主鍵。

BorrowRecord資料表:包含 pId、callNo、borrowDate、returnDate,也就是此資料表為借書資料表,有會員編號、書籍編號、借書日期以及歸還日期,而前三個欄位,也就是 pId、callNo與 borrowDate三個欄位為複合主鍵,不能重複。

想看到的結果
1.找出張三(為一讀者姓名)所借過的所有書的分類號和書名,一本書只能列出一次
2.請列出現電腦類(即 subject='Computer') 每一本書籍的 ISBN,書名和被借閱的次數
請用一個SQL敘述表達
3.對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數

這三個問題,是當年高考的三個問題,第一題算是相當簡單,但第二題與第三題則算是相當困難,尤其是第三題,在沒有任何電腦的高考試場中,要能夠徒手寫出答案的,真的相當不容易,我個人也是很懷疑教授要怎麼改這張考卷,因為 SQL指令並沒有所謂的「標準答案」,可能 N種人有 N種不同的寫法,而且如果真要注意的話,應該要寫 ANSI-SQL的指令比較好,不然你真的寫只有 SQL Server才能支援的句法,對批改者可能會認為你的答案是「錯誤的」那就糟糕了。

所幸我們並非是要「徒手」將這些資料在不透過電腦的情況下完成,所以我們可以先把一些假資料先建立起來,再看要如何撰寫這樣的 SQL指令。

use tempdb
go
create table Book (callNo int ,isbn char(10) ,title varchar(20) ,subject varchar(20))
go
--分類號,ISBN,書名,主題

create table Patron(pId int ,name varchar(20) ,startDate datetime)
--讀者編號,讀者姓名,開始日期

create table BorrowRecord(pId int ,callNo int ,borrowDate datetime ,returnDate datetime)
go
insert Patron values (1,'張三','2005/12/31')                   
insert Patron values (2,'李四','2006/1/1')                   
go
insert Book values (1,'1111111111','SQL Server 2008','Computer')
insert Book values (2,'2222222222','心靈成長系列1','心靈')
insert BorrowRecord values (1,1,'2009/1/1','2009/1/3')
insert BorrowRecord values (1,1,'2009/1/3','2009/1/4')
insert BorrowRecord values (1,2,'2009/1/3','2009/1/4')
insert BorrowRecord values (1,2,'2009/1/4','2009/1/5')
insert BorrowRecord values (1,2,'2009/1/5','2009/1/6')
insert BorrowRecord values (1,2,'2009/1/6','2009/1/7')
insert BorrowRecord values (1,2,'2009/1/7','2009/1/8')
insert BorrowRecord values (1,2,'2009/1/8','2009/1/9')
insert BorrowRecord values (1,2,'2009/1/9','2009/1/10')
insert BorrowRecord values (1,2,'2009/1/10','2009/1/11')
insert BorrowRecord values (1,2,'2009/1/11','2009/1/12')
insert BorrowRecord values (2,1,'2009/1/11','2009/1/12')
go

有了資料之後,會不會比較有「感覺」一點呢?

接下來我們再來看這三個問題:
1.找出張三(為一讀者姓名)所借過的所有書的分類號和書名,一本書只能列出一次

這題算是最簡單的,因為題目講到的資料表,就是全部都有,也就是張三(屬於Patron資料表)所借過的(BorrowRecord)所有書(Book),所以這題無論怎麼寫,就是三個資料表關聯的寫法,而所需的是「分類號」與「書名」,為了怕重複就用 distinct即可(不過 distinct不是 ansi SQL的寫法,為了保險起見可能要用 group by來完成)。

所以第一題可能有兩種寫法,分別如下:

select distinct book.callNo,title from Patron inner join BorrowRecord
on Patron.pId =BorrowRecord.pId
inner join Book
on Book.callNo = BorrowRecord.callNo
where patron.name ='張三'
利用 distinct的方式避免資料重複或是利用

select book.callNo,title from Patron inner join BorrowRecord
on Patron.pId =BorrowRecord.pId
inner join Book
on Book.callNo = BorrowRecord.callNo
where patron.name ='張三'
group by callNo,title

group by的方式避免資料重複。

第一個問題算是基本題,難免嘛,考試第一題就出殺招,之後要怎麼再交流?所以可以遇見的是,第二題,第三題會愈來愈難。

接下來看第二題:
2.請列出現電腦類(即 subject='Computer') 每一本書籍的 ISBN,書名和被借閱的次數
請用一個SQL敘述表達。

題目特別說「要用一個 SQL敘述表達」,代表你如果分開寫是很多人習慣的寫法,畢竟 SQL指令甚至 SQL程式都可以用一些變數來達成目的,不過這是考試,所以要求你用單一句 SQL指令達成也是合情合理。

其實看到這個「請用一個 SQL敘述表達」,就知道該是次查詢出馬的時間了,既然不能寫成多行指令,勢必要把 SQL指令「包成」多行 SQL指令,並且使用次查詢達成生產線流程的目的。

此題會影響的資料表總共有Book(每一本書的 ISBN)和BorrowRecord資料表(被借閱的次數),因為並沒有要求讀者的資料,所以勢必這兩個資料表要關聯(當然如果兩資料表沒有直接關聯,那麼讀者可能也要拉進來關聯,但此題不用,因為兩個資料表可以直接關聯沒問題)。

這裡唯一的問題是「被借閱的次數」,以及所需要呈現出來的資料要isbn與書名這兩個欄位。這裡的解題重點在,如果你要撰寫「被借閱次數」的話,勢必要使用 group by的語法進行計算,而若你是使用 group by的語法,那除非你把 isbn與書名一起 group by,不然就只能夠 group by一個欄位,回想起來,這也是為什麼題目要我們用一個 SQL指令來完成任務了。

不過,依據我們先前的生產線理論我可以把「被借閱次數」呈現,以及 where找出 subject為電腦類的部分,所以部分程式看起來如下:
select isbn,COUNT(isbn) as counts from BorrowRecord inner join book
on book.callno = borrowrecord.callno
where subject='computer'
group by isbn

BorrowRecord與 Book資料表之間的關聯之前已經預料到,而「被借閱次數」則是使用 count(isbn)與 group by的用法計算出來,此例並沒有使用 group by callNo而是使用 group by isbn,主要是因為題目最後要呈現的是 isbn,而剛好 isbn是不會重複的資料,不然可以使用 group by callNo(主鍵)的方式完成。

這個句子完成,只會出現 isbn 借閱次數這兩個欄位,雖然有 filter subject ='computer',不過我們想看的最後結果是書名,所以這時把剛才句子完成的結果集,再與 Book資料表關聯,即可完成我們最後的答案!對的,也就是最後要使用次查詢,把剛才的結果集變成一個資料表,與 Book資料表關聯以取得書名資料,所以最後的指令如下:

select b.isbn,Book.title,b.counts from (
select isbn,COUNT(isbn) as counts from BorrowRecord inner join book
on book.callno = borrowrecord.callno
where subject='computer'
group by isbn ) as b
inner join Book
on b.isbn = Book.isbn

這裡只是特別示範如何把資料取出時,以生產線的流程將來源轉換成你想看到的結果,次查詢的資料是第一個結果集,變成一個次查詢後再與 Book資料表關聯變成最後的結果集。
也就是說,此次的生產流程如下:

來源→利用 group by產生被借閱次數與 filter→與 Book Join以取出書名→結果

中間的轉換動作,總共使用了兩次,所以還算是一個普通麻煩的指令。

接下來我們看第三題:

3.對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數

一看問題,就知道問題不單純,影響到的資料表有:1.讀者(Patron) 2.借閱次數(BorrowRecord)以及3.isbn(Book)這三個資料表,而且問題複雜到,必需要把「借閱次數超過10次以上的讀者抓出來後」,再列出讀者編號以及「2009年的借閱總次數」,所以此題我們先以生產線的流程抓一下解題順序:

來源→取出借閱次數超過10次以上的讀者→列出讀者編號與2009年的借閱總次數→結果。

所以,這看起來也是一個次查詢的應用,首先必需要把借閱次數超過10次以上的讀者取出,以此為次查詢,再包覆著其他資料表關聯,最後得到我們要的結果,所以按順序第一步該這麼做:

select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10

這個程式是將借閱次數超過10次以上的讀者取出。
以此產生的結果集,再與去年的借閱次數關聯,然後再取出借閱總次數。

不過此時發現一個問題,2009年的借閱總次數同樣也需要使用 group by的句法,所以我們必需把前兩個句子合起來,再包成一個次查詢,然後再使用 group by才能達成我們的結果,換句話說,第一句與第二句的程式如下:

select B.pId,B.borrowDate  as counts from
(
select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10
) as a
inner join Borrowrecord as B
on a.pId = B.pId
where B.borrowDate between '2009/1/1' and '2009/12/31'

這個範例請與第一句一起看,第一句子是取出借閱次數超過10次以上的讀者,與第二句關聯,取出借閱日期在2009年的資料,但如果只有這兩句,並沒有辦法再得到2009年的總借閱次數,為此,我們還需要再把這個結果集包起來,再計算借閱總次數,所以最後的結果是:

select p.pId,count(*) as  借閱總次數 from
(
select B.pId,B.borrowDate  as counts from
(
select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10
) as a
inner join Borrowrecord as B
on a.pId = B.pId
where B.borrowDate between '2009/1/1' and '2009/12/31'
) as p
group by p.pId

幸好這個例子只需要 id與借閱總次數而不用其他資料,不然又得麻煩了,還得包第三次才能得到我們的結果。

不過我必需先強調,這只是依照題目的問題,依生產線的流程,一層層取得我們的解題順序,並不是說它就是最佳寫法或是沒有其他方法可以完成。這也就是其實很多人至今還是很愛使用次查詢來解決複雜的 SQL流程,主要的原因在於,按資料查詢的順序,一步步將你要的資料按步就班的將結果產生,這樣也是很直覺的方法。

結語


本期大概介紹我們前期所提及的寫 SQL指令的生產線流程,這次的範例稍微複雜了一點,所使用的流程也稍微多了一點,不過平常的 SQL指令應該不至於如此困難,這些範例大多都是考試的例子,或是一些實務上有人問到的例子,之前還會有類似的範例,各位可以先看問題,先自已想一下答案該如何做,最後再自行測試結果。

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

而上一期的點歌系統,有來源與想看的結果,只是看中間如何拚湊出轉換的指令。
借書系統
這個問題也是某年高考的資料庫考題,請考生分析資料庫,並且利用 SQL指令取得原題意想要的結果,本題是一個借書系統,請看下面的資料庫設計,以及問題。不過在看這個資料庫之前,其實你心裡應該大概也有個底,知道借書系統可能會有哪些原素在裡面,例如借書人的資訊,借了哪些書,以及書的資料等等,然後你想要從這些資料庫之中如何撈出資料。
設計出來的資料庫
我們在此把借書系統的資料庫設計出來如下:

Book資料表:包含 callNo、isbn以及title、subject,也就是書的編號(主鍵)、isbn碼、書名與主題的資訊,這裡不是以 isbn碼當成主索引鍵而是採用 callNo當成一個替代鍵。

Patron資料表:包含 pId、name與startDate,也就是讀者資料中的編號、姓名與加入會員日,其中 pId為主鍵。

BorrowRecord資料表:包含 pId、callNo、borrowDate、returnDate,也就是此資料表為借書資料表,有會員編號、書籍編號、借書日期以及歸還日期,而前三個欄位,也就是 pId、callNo與 borrowDate三個欄位為複合主鍵,不能重複。

想看到的結果
1.找出張三(為一讀者姓名)所借過的所有書的分類號和書名,一本書只能列出一次
2.請列出現電腦類(即 subject='Computer') 每一本書籍的 ISBN,書名和被借閱的次數
請用一個SQL敘述表達
3.對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數

這三個問題,是當年高考的三個問題,第一題算是相當簡單,但第二題與第三題則算是相當困難,尤其是第三題,在沒有任何電腦的高考試場中,要能夠徒手寫出答案的,真的相當不容易,我個人也是很懷疑教授要怎麼改這張考卷,因為 SQL指令並沒有所謂的「標準答案」,可能 N種人有 N種不同的寫法,而且如果真要注意的話,應該要寫 ANSI-SQL的指令比較好,不然你真的寫只有 SQL Server才能支援的句法,對批改者可能會認為你的答案是「錯誤的」那就糟糕了。

所幸我們並非是要「徒手」將這些資料在不透過電腦的情況下完成,所以我們可以先把一些假資料先建立起來,再看要如何撰寫這樣的 SQL指令。

use tempdb
go
create table Book (callNo int ,isbn char(10) ,title varchar(20) ,subject varchar(20))
go
--分類號,ISBN,書名,主題

create table Patron(pId int ,name varchar(20) ,startDate datetime)
--讀者編號,讀者姓名,開始日期

create table BorrowRecord(pId int ,callNo int ,borrowDate datetime ,returnDate datetime)
go
insert Patron values (1,'張三','2005/12/31')                   
insert Patron values (2,'李四','2006/1/1')                   
go
insert Book values (1,'1111111111','SQL Server 2008','Computer')
insert Book values (2,'2222222222','心靈成長系列1','心靈')
insert BorrowRecord values (1,1,'2009/1/1','2009/1/3')
insert BorrowRecord values (1,1,'2009/1/3','2009/1/4')
insert BorrowRecord values (1,2,'2009/1/3','2009/1/4')
insert BorrowRecord values (1,2,'2009/1/4','2009/1/5')
insert BorrowRecord values (1,2,'2009/1/5','2009/1/6')
insert BorrowRecord values (1,2,'2009/1/6','2009/1/7')
insert BorrowRecord values (1,2,'2009/1/7','2009/1/8')
insert BorrowRecord values (1,2,'2009/1/8','2009/1/9')
insert BorrowRecord values (1,2,'2009/1/9','2009/1/10')
insert BorrowRecord values (1,2,'2009/1/10','2009/1/11')
insert BorrowRecord values (1,2,'2009/1/11','2009/1/12')
insert BorrowRecord values (2,1,'2009/1/11','2009/1/12')
go

有了資料之後,會不會比較有「感覺」一點呢?

接下來我們再來看這三個問題:
1.找出張三(為一讀者姓名)所借過的所有書的分類號和書名,一本書只能列出一次

這題算是最簡單的,因為題目講到的資料表,就是全部都有,也就是張三(屬於Patron資料表)所借過的(BorrowRecord)所有書(Book),所以這題無論怎麼寫,就是三個資料表關聯的寫法,而所需的是「分類號」與「書名」,為了怕重複就用 distinct即可(不過 distinct不是 ansi SQL的寫法,為了保險起見可能要用 group by來完成)。

所以第一題可能有兩種寫法,分別如下:

select distinct book.callNo,title from Patron inner join BorrowRecord
on Patron.pId =BorrowRecord.pId
inner join Book
on Book.callNo = BorrowRecord.callNo
where patron.name ='張三'
利用 distinct的方式避免資料重複或是利用

select book.callNo,title from Patron inner join BorrowRecord
on Patron.pId =BorrowRecord.pId
inner join Book
on Book.callNo = BorrowRecord.callNo
where patron.name ='張三'
group by callNo,title

group by的方式避免資料重複。

第一個問題算是基本題,難免嘛,考試第一題就出殺招,之後要怎麼再交流?所以可以遇見的是,第二題,第三題會愈來愈難。

接下來看第二題:
2.請列出現電腦類(即 subject='Computer') 每一本書籍的 ISBN,書名和被借閱的次數
請用一個SQL敘述表達。

題目特別說「要用一個 SQL敘述表達」,代表你如果分開寫是很多人習慣的寫法,畢竟 SQL指令甚至 SQL程式都可以用一些變數來達成目的,不過這是考試,所以要求你用單一句 SQL指令達成也是合情合理。

其實看到這個「請用一個 SQL敘述表達」,就知道該是次查詢出馬的時間了,既然不能寫成多行指令,勢必要把 SQL指令「包成」多行 SQL指令,並且使用次查詢達成生產線流程的目的。

此題會影響的資料表總共有Book(每一本書的 ISBN)和BorrowRecord資料表(被借閱的次數),因為並沒有要求讀者的資料,所以勢必這兩個資料表要關聯(當然如果兩資料表沒有直接關聯,那麼讀者可能也要拉進來關聯,但此題不用,因為兩個資料表可以直接關聯沒問題)。

這裡唯一的問題是「被借閱的次數」,以及所需要呈現出來的資料要isbn與書名這兩個欄位。這裡的解題重點在,如果你要撰寫「被借閱次數」的話,勢必要使用 group by的語法進行計算,而若你是使用 group by的語法,那除非你把 isbn與書名一起 group by,不然就只能夠 group by一個欄位,回想起來,這也是為什麼題目要我們用一個 SQL指令來完成任務了。

不過,依據我們先前的生產線理論我可以把「被借閱次數」呈現,以及 where找出 subject為電腦類的部分,所以部分程式看起來如下:
select isbn,COUNT(isbn) as counts from BorrowRecord inner join book
on book.callno = borrowrecord.callno
where subject='computer'
group by isbn

BorrowRecord與 Book資料表之間的關聯之前已經預料到,而「被借閱次數」則是使用 count(isbn)與 group by的用法計算出來,此例並沒有使用 group by callNo而是使用 group by isbn,主要是因為題目最後要呈現的是 isbn,而剛好 isbn是不會重複的資料,不然可以使用 group by callNo(主鍵)的方式完成。

這個句子完成,只會出現 isbn 借閱次數這兩個欄位,雖然有 filter subject ='computer',不過我們想看的最後結果是書名,所以這時把剛才句子完成的結果集,再與 Book資料表關聯,即可完成我們最後的答案!對的,也就是最後要使用次查詢,把剛才的結果集變成一個資料表,與 Book資料表關聯以取得書名資料,所以最後的指令如下:

select b.isbn,Book.title,b.counts from (
select isbn,COUNT(isbn) as counts from BorrowRecord inner join book
on book.callno = borrowrecord.callno
where subject='computer'
group by isbn ) as b
inner join Book
on b.isbn = Book.isbn

這裡只是特別示範如何把資料取出時,以生產線的流程將來源轉換成你想看到的結果,次查詢的資料是第一個結果集,變成一個次查詢後再與 Book資料表關聯變成最後的結果集。
也就是說,此次的生產流程如下:

來源→利用 group by產生被借閱次數與 filter→與 Book Join以取出書名→結果

中間的轉換動作,總共使用了兩次,所以還算是一個普通麻煩的指令。

接下來我們看第三題:

3.對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數

一看問題,就知道問題不單純,影響到的資料表有:1.讀者(Patron) 2.借閱次數(BorrowRecord)以及3.isbn(Book)這三個資料表,而且問題複雜到,必需要把「借閱次數超過10次以上的讀者抓出來後」,再列出讀者編號以及「2009年的借閱總次數」,所以此題我們先以生產線的流程抓一下解題順序:

來源→取出借閱次數超過10次以上的讀者→列出讀者編號與2009年的借閱總次數→結果。

所以,這看起來也是一個次查詢的應用,首先必需要把借閱次數超過10次以上的讀者取出,以此為次查詢,再包覆著其他資料表關聯,最後得到我們要的結果,所以按順序第一步該這麼做:

select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10

這個程式是將借閱次數超過10次以上的讀者取出。
以此產生的結果集,再與去年的借閱次數關聯,然後再取出借閱總次數。

不過此時發現一個問題,2009年的借閱總次數同樣也需要使用 group by的句法,所以我們必需把前兩個句子合起來,再包成一個次查詢,然後再使用 group by才能達成我們的結果,換句話說,第一句與第二句的程式如下:

select B.pId,B.borrowDate  as counts from
(
select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10
) as a
inner join Borrowrecord as B
on a.pId = B.pId
where B.borrowDate between '2009/1/1' and '2009/12/31'

這個範例請與第一句一起看,第一句子是取出借閱次數超過10次以上的讀者,與第二句關聯,取出借閱日期在2009年的資料,但如果只有這兩句,並沒有辦法再得到2009年的總借閱次數,為此,我們還需要再把這個結果集包起來,再計算借閱總次數,所以最後的結果是:

select p.pId,count(*) as  借閱總次數 from
(
select B.pId,B.borrowDate  as counts from
(
select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10
) as a
inner join Borrowrecord as B
on a.pId = B.pId
where B.borrowDate between '2009/1/1' and '2009/12/31'
) as p
group by p.pId

幸好這個例子只需要 id與借閱總次數而不用其他資料,不然又得麻煩了,還得包第三次才能得到我們的結果。

不過我必需先強調,這只是依照題目的問題,依生產線的流程,一層層取得我們的解題順序,並不是說它就是最佳寫法或是沒有其他方法可以完成。這也就是其實很多人至今還是很愛使用次查詢來解決複雜的 SQL流程,主要的原因在於,按資料查詢的順序,一步步將你要的資料按步就班的將結果產生,這樣也是很直覺的方法。

結語
本期大概介紹我們前期所提及的寫 SQL指令的生產線流程,這次的範例稍微複雜了一點,所使用的流程也稍微多了一點,不過平常的 SQL指令應該不至於如此困難,這些範例大多都是考試的例子,或是一些實務上有人問到的例子,之前還會有類似的範例,各位可以先看問題,先自已想一下答案該如何做,最後再自行測試結果。

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

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

月分類Month List