1. SQL的弱點
1.1. SQL陳述句的執行結果轉換為想要的格式
-
1.1.1. 格式轉換
-
1.1.2. SQL語言本來就不是為了這個目的而出現的
-
1.1.3. SQL終究也只是主要用于查詢資料的語言而已
1.2. 生成報表的功能
- 1.2.1. 視窗函式
1.3. SQL不是用來生成報表的語言，所以不建議用它來進行格式轉換
- 1.3.1. 必要時考慮用外連接或CASE運算式來解決問題
2. 制作交叉表（行→列）
2.1. 示例
- 2.1.1.
- 2.1.1.1. --水平展開求交叉表(1)：使用外連接
SELECT C0.name,
CASE WHEN C1.name IS NOT NULL THEN'○'ELSE NULL END AS "SQL入門",
CASE WHEN C2.name IS NOT NULL THEN'○'ELSE NULL END AS "UNIX基礎",
CASE WHEN C3.name IS NOT NULL THEN'○'ELSE NULL END AS "Java中級"
FROM (SELECT DISTINCT name FROM Courses) C0 --這里的C0是側欄
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'SQL入門’) C1
ON C0.name = C1.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'UNIX基礎’) C2
ON C0.name = C2.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Java中級’) C3
ON C0.name = C3.name;
-
2.1.1.2. 一般情況下，外連接都可以用標量子查詢替代
2.1.1.2.1. 需要增加或者減少課程時，只修改SELECT子句即可，代碼修改起來比較簡單
2.1.1.2.2. 利于應對需求變更，對于需要動態生成SQL的系統也是很有好處的
2.1.1.2.3. 性能不太好
-
2.1.1.3. --水平展開(2)：使用標量子查詢
SELECT C0.name,
(SELECT '○'
FROM Courses C1
WHERE course = 'SQL入門’
AND C1.name = C0.name) AS "SQL入門",
(SELECT '○'
FROM Courses C2
WHERE course = 'UNIX基礎’
AND C2.name = C0.name) AS "UNIX基礎",
(SELECT '○'
FROM Courses C3
WHERE course = 'Java中級’
AND C3.name = C0.name) AS "Java中級"
FROM (SELECT DISTINCT name FROM Courses) C0; --這里的C0是表側欄
-
2.1.1.4. 嵌套使用CASE運算式
2.1.1.4.1. CASE運算式可以寫在SELECT子句里的聚合函式內部，也可以寫在聚合函式外部
2.1.1.4.2. 其實在SELECT子句里，聚合函式的執行結果也是標量值，因此可以像常量和普通列一樣使用
2.1.1.4.3. 和標量子查詢的做法一樣簡潔，也能靈活地應對需求變更
-
2.1.1.5. --水平展開(3)：嵌套使用CASE運算式
SELECT name,
CASE WHEN SUM(CASE WHEN course = 'SQL入門’THEN 1 ELSE NULL END) = 1
THEN'○'ELSE NULL END AS "SQL入門",
CASE WHEN SUM(CASE WHEN course = 'UNIX基礎’THEN 1 ELSE NULL END) = 1
THEN'○'ELSE NULL END AS "UNIX基礎",
CASE WHEN SUM(CASE WHEN course = 'Java中級’THEN 1 ELSE NULL END) = 1
THEN'○'ELSE NULL END AS "Java中級"
FROM Courses
GROUP BY name;
3. 匯總重復項于一列（列→行）
3.1. 示例
- 3.1.1.
- 3.1.1.1. --列資料轉換成行資料：使用UNION ALL
SELECT employee, child_1 AS child FROM Personnel
UNION ALL
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;
- 3.1.1.2. 視圖
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel
UNION
SELECT child_3 FROM Personnel;
child
-----
一郎
二郎
三郎
春子
夏子
3.1.1.2.1. --獲取員工子女串列的SQL陳述句（沒有孩子的員工也要輸出）
SELECT EMP.employee, CHILDREN.child
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3);
4. 制作嵌套式表側欄
4.1. 示例
- 4.1.1.
- 4.1.2.
-
4.1.3. 結果
- 4.1.3.1.
- 4.1.4. --使用外連接生成嵌套式表側欄：錯誤的SQL陳述句
SELECT MASTER1.age_class AS age_class,
MASTER2.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1--外連接1：和年齡層級主表進行外連接
ON MASTER1.age_class = DATA.age_class
RIGHT OUTER JOIN TblSex MASTER2--外連接2：和性別主表進行外連接
ON MASTER2.sex_cd = DATA.sex_cd;
- 4.1.4.1. --停在第1個外連接處時：結果里包含年齡層級為2的資料
SELECT MASTER1.age_class AS age_class,
DATA.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
RIGHT OUTER JOIN TblAge MASTER1
ON MASTER1.age_class = DATA.age_class;
-
4.1.5. 如果不允許進行兩次外連接，那么調整成一次就可以了
-
4.1.6. 對于不支持CROSS JOIN陳述句的資料庫，可以像FROM TblAge,TblSex這樣不指定連接條件，把需要連接的表寫在一起，其效果與交叉連接一樣
-
4.1.7. 如果先生成主表的笛卡兒積再進行連接，很容易就可以完成
-
4.1.8. --使用外連接生成嵌套式表側欄：正確的SQL陳述句
SELECT MASTER.age_class AS age_class,
MASTER.sex_cd AS sex_cd,
DATA.pop_tohoku AS pop_tohoku,
DATA.pop_kanto AS pop_kanto
FROM (SELECT age_class, sex_cd
FROM TblAge CROSS JOIN TblSex ) MASTER --使用交叉連接生成兩張主表的笛卡兒積
LEFT OUTER JOIN
(SELECT age_class, sex_cd,
SUM(CASE WHEN pref_name IN (’青森’, ’秋田’)
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN (’東京’, ’千葉’)
THEN population ELSE NULL END) AS pop_kanto
FROM TblPop
GROUP BY age_class, sex_cd) DATA
ON MASTER.age_class = DATA.age_class
AND MASTER.sex_cd = DATA.sex_cd;
5. 作為乘法運算的連接
5.1. 示例
- 5.1.1.
- 5.1.2. --解答(1)：通過在連接前聚合來創建一對一的關系
SELECT I.item_no, SH.total_qty
FROM Items I LEFT OUTER JOIN
(SELECT item_no, SUM(quantity) AS total_qty
FROM SalesHistory
GROUP BY item_no) SH
ON I.item_no = SH.item_no;
-
5.1.2.1. 以商品編號為主鍵的臨時視圖
-
5.1.2.2. 無法利用索引優化查詢
-
5.1.3. --解答(2)：先進行一對多的連接再聚合
SELECT I.item_no, SUM(SH.quantity) AS total_qty
FROM Items I LEFT OUTER JOIN SalesHistory SH
ON I.item_no = SH.item_no 一對多的連接
GROUP BY I.item_no;
-
5.1.3.1. 代碼更簡潔
-
5.1.3.2. 沒有使用臨時視圖，所以性能也會有所改善
5.2. 從行數來看，表連接可以看成乘法，因此，當表之間是一對多的關系時，連接后行數不會增加
6. 全外連接
6.1. FULL OUTER JOIN
6.2. 相當于求集合的和（UNION，也稱并集）
- 6.2.1. 內連接相當于求集合的積（INTERSECT，也稱交集）
6.3. 示例
- 6.3.1. --全外連接保留全部資訊
SELECT COALESCE(A.id, B.id) AS id,
A.name AS A_name,
B.name AS B_name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id;
- 6.3.1.1. --資料庫不支持全外連接時的替代方案
SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id;
6.3.1.1.1. 分別進行左外連接和右外連接，再把兩個結果通過UNION合并起來
6.4. COALESCE是SQL的標準函式
- 6.4.1. 可以接受多個引數，功能是回傳第一個非NULL的引數
6.5. 外連接的思想和集合運算很像，使用外連接可以實作各種集合運算
7. 用外連接求差集：B－A
7.1. 示例
- 7.1.1.
SELECT B.id AS id, B.name AS B_name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL;
7.2. 可以作為NOT IN和NOT EXISTS之外的另一種解法
7.3. 可能是差集運算中效率最高的
8. 用全外連接求異或集
8.1. SQL沒有定義求異或集的運算子
8.2. 用集合運算子
-
8.2.1. (A UNION B) EXCEPT (A INTERSECT B)
-
8.2.2. (A EXCEPT B) UNION (B EXCEPT A)
-
8.2.3. 性能開銷大
8.3. 示例
- 8.3.1.
SELECT COALESCE(A.id, B.id) AS id,
COALESCE(A.name , B.name ) AS name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL
OR B.name IS NULL;
9. 用外連接進行關系除法
9.1. 示例
- 9.1.1. --用外連接進行關系除法運算：差集的應用
SELECT DISTINCT shop
FROM ShopItems SI1
WHERE NOT EXISTS
(SELECT I.item
FROM Items I LEFT OUTER JOIN ShopItems SI2
ON I.item = SI2.item
AND SI1.shop = SI2.shop
WHERE SI2.item IS NULL) ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548993.html
標籤：其他
下一篇：mysql總結