第九章 進階的查詢技巧

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

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

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

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

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

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

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

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]

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

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]

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

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

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

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.欄位值二

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

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

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

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

最常用的次查詢指令 需要兩個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'

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