第九章 進階的查詢技巧.

Presentation on theme: "第九章 進階的查詢技巧."— Presentation transcript:

1 第九章 進階的查詢技巧

2 章節概覽 分組指令GROUP BY 關連兩個以上的資料表：JOIN指令 利用UNION合併多個查詢結果 次查詢

3 9.1 分組指令GROUP BY GROUP BY是選定某個欄位做分組的動作

4 GROUP BY語法 SELECT 欄位名稱1,集總函數名稱 FROM 資料表GROUP BY 欄位 名稱1
一般會配合彙總函數，彙總函數： 會執行一組值的計算並傳回單一值。除了 COUNT 以外，彙總 函數會忽略所有 Null 的值。 AVG COUNT MAX MIN STDEV SUM VAR

5 GROUP BY例子 SELECT orderid,SUM(quantity) AS 總數量
範例9-1 SELECT orderid,SUM(quantity) AS 總數量 FROM [Order Details] GROUP BY orderid

6 GROUP BY與WHERE、HAVING(1/2)
範例9-3 WHERE指令要寫在GROUP BY指令的前面 SELECT OrderID,SUM(Quantity) AS 總數量 FROM [Order Details] WHERE OrderID = 10248 GROUP BY OrderID

7 GROUP BY與WHERE、HAVING(2/2)
範例9-4 HAVING是尋找集總函式欄位的值 SELECT OrderID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID HAVING SUM(Quantity)> 30

8 GROUP BY與WITH ROLLUP (1/2)
SELECT OrderID,ProductID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID,ProductID WITH ROLLUP 範例9-5

9 GROUP BY與WITH ROLLUP (2/2)
SELECT OrderID,ProductID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID,ProductID + SELECT OrderID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID SELECT SUM(Quantity) AS 總數量 FROM [Order Details]

10 GROUP BY與WITH CUBE (1/2) SELECT OrderID,ProductID,SUM(Quantity) AS 總數量
FROM [Order Details] GROUP BY OrderID,ProductID WITH CUBE 範例9-6

11 GROUP BY與WITH CUBE (2/2) WITH CUBE等於下列指令相加
SELECT OrderID,ProductID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID,ProductID + SELECT OrderID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY OrderID SELECT ProductID,SUM(Quantity) AS 總數量 FROM [Order Details] GROUP BY ProductID SELECT SUM(Quantity) AS 總數量 FROM [Order Details]

12 9.2 關連兩個以上的資料表：JOIN指令 INNER JOIN OUTER JOIN RIGHT OUTER JOIN
LEFT OUTER JOIN FULL OUTER JOIN SELF JOIN

13 9.2.1 INNER JOIN 的使用(1/2) 將兩個資料表間相同key值的資料取出
SELECT 欄位 FROM 資料表A INNER JOIN 資料表B ON 資料表A.欄位值一=資料表B.欄位值二

14 INNER JOIN 的使用(2/2) 將相同導師編號的學生資料取出 SELECT 學號,學生姓名,導師姓名 FROM 學生
ON 學生.導師編號 = 導師.導師編號 課本P.9.10

15 9.2.2 OUTER JOIN 的使用(1/2) 不管兩個資料表間有無相同key值資料，選擇將其 中一邊資料表的資料取出
分為LEFT與OUTER和FULL OUTER JOIN SELECT 欄位 FROM 資料表A LEFT OUTER JOIN 資料表B ON 資料表A.欄位值一=資料表B.欄位值二 SELECT 欄位 FROM 資料表A RIGHT OUTER JOIN 資料表B ON 資料表A.欄位值一=資料表B.欄位值二 SELECT 欄位 FROM 資料表A FULL OUTER JOIN 資料表B ON 資料表A.欄位值一=資料表B.欄位值二

16 OUTER JOIN 的使用(2/2) 不管兩個資料表間有無相同key值資料，選擇將其 中一邊資料表的資料取出
課本 P.9-14 SELECT 導師.導師編號,導師姓名,學生姓名 FROM 學生 RIGHT OUTER JOIN 導師 ON 學生.導師編號 = 導師.導師編號

17 9.2.3 SELF JOIN 的使用 資料表對自己做JOIN的工作 擅用資料表別名(Alias)的觀念
SELECT a.學號,a.學生姓名,b.學號,b.學生姓名,a.興趣 FROM 學生 a INNER JOIN 學生 b ON a.興趣 = b.興趣 AND a.學號 <> b.學號 範例9-13

18 9.3 利用UNION合併多個查詢結果 UNION: 主要是將兩個相同欄位的不同名稱資料表聯結在 一起 兩個資料表的欄位定義與資料型別要相同
SELECT * FROM CustomerMexico UNION SELECT * FROM CustomerGermany 思考：重覆資料會顯示幾次？

19 9.4 次查詢 一行查詢指令有可能會有一個以上的SELECT指令 找出和 King先生所住的城市是同樣的員工有哪些？
SELECT * FROM Employees WHERE City= (King先生所居住的城市) 變成次查詢的寫法： SELECT * FROM Employees WHERE City= (SELECT City FROM Employees WHERE LastName ='King')

20 最常用的次查詢指令 需要兩個SELECT指令，甚至三個 SELECT指令， 才能完成全部的查詢 USE pubs
SELECT title, price, (SELECT AVG(price) FROM titles) AS 平均,price-(SELECT AVG(price) FROM titles) AS 差異 FROM titles WHERE type='popular_comp'

21 學習成果回顧 可以利用GROUP BY將指定欄位做分組，並計算出 集總函式所得到的值
GROUP BY後面可以利用WITH CUBE或WITH ROLLUP ，將可能的情況都抓取出來 可以利用JOIN將兩個以上的資料表關連在一起 SubQuery次查詢方便你將多個查詢指令，合成一個 查詢指令來寫