數(shù)據(jù)庫中的join操作不一定是兩個(gè)不同的表循未，同一張表也可以自己連接自己麻昼，只是需要起個(gè)別名塞关，合理的使用自連接可以完成一些巧妙的操作蜒犯。
自連接
1.實(shí)現(xiàn)商品的所有排列組合
直接用cross join就可以實(shí)現(xiàn)
- 去掉和自身重復(fù)的
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 CROSS JOIN Products P2
ON P1.`name` <> P2.`name`;
3.不區(qū)分排列組合的順序
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 CROSS JOIN Products P2
ON P1.`name` < P2.`name`;
如果想獲取三個(gè)以上元素可以基于該語句擴(kuò)展，where后面依舊加相應(yīng)條件硫戈。
- 查找重復(fù)行
如下圖找出重復(fù)的水果保留row_id最大的锰什，實(shí)際上不論id是字符串還是數(shù)字都可以用符號(hào)比較大小，row_id不一定是數(shù)值型丁逝。
這是關(guān)聯(lián)子查詢
SELECT p1.row_id,name from Products p1
where p1.row_id < (SELECT MAX(row_id) from Products p2 where p2.`name` = p1.name and p1.price = p2.price)
非等值自連接
SELECT P1.* FROM Products P1 JOIN Products P2 ON
P1.`name` = P2.`name` AND P1.price = P2.price AND P1.row_id < P2.row_id GROUP BY P1.row_id
刪除在mysql中不是直接把SELECT換成DELETE就可以實(shí)現(xiàn)的汁胆，應(yīng)該把要?jiǎng)h除的選出來放在一個(gè)中間表，然后再刪除霜幼。
DELETE FROM Products where EXISTS (SELECT * FROM (
SELECT P1.* FROM Products P1
WHERE EXISTS (SELECT *
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price
AND P1.row_id < P2.row_id ))t);
5.數(shù)據(jù)編號(hào)排序
SELECT P1.name,
P1.price,
(SELECT COUNT(P2.price)
FROM Products P2
WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;
也可以使用自連接的方式實(shí)現(xiàn)
SELECT p1.name,p1.price, COUNT(p2.price) + 1 AS RANK FROM Products p1 LEFT JOIN Products p2
ON p1.price < p2.price GROUP BY p1.`name` ORDER BY RANK```
這里一定是要左連接嫩码，因?yàn)榈谝幻趐1.price < p2.price 條件下不會(huì)出現(xiàn)在p1里面，必須使用COUNT(P2.price)不能用COUNT(*),如果需要不跳過價(jià)格相同的名詞罪既，只需要COUNT(DISTINCT p2.price) 就可以了铸题。
練習(xí)題：
因?yàn)槭墙M合铡恕，所以（香蕉, 橘子）和（橘子, 香蕉）這樣順序相反的對(duì)被視為相同的對(duì)。此外丢间，因?yàn)樵试S重復(fù)探熔，所以結(jié)果里也出現(xiàn)了（橘子, 橘子）這樣的對(duì)。
SELECT P1.NAME,P2.NAME FROM Products P1 JOIN Products P2
ON P1.`name` >= P2.`name`
SELECT P1.district,P1.name,P1.price,COUNT(P2.price) + 1 AS RANK FROM DistrictProducts P1
LEFT JOIN DistrictProducts P2 ON P1.district = P2.district and P1.price < P2.price
GROUP BY P1.district,P1.`name` ORDER BY P1.district,RANK
外連接
1.表格的格式轉(zhuǎn)換
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 基礎(chǔ)",
CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java 中級(jí)"
FROM (SELECT DISTINCT name FROM Courses) C0 -- 這里的C0 是側(cè)欄
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 基礎(chǔ)' ) C2
ON C0.name = C2.name
LEFT OUTER JOIN
(SELECT name FROM Courses WHERE course = 'Java 中級(jí)' ) C3
ON C0.name = C3.name;
或者用CASE在SELECT里面做選擇烘挫，類似于前面提到的統(tǒng)計(jì)某個(gè)州縣的男女?dāng)?shù)量一樣诀艰。
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基礎(chǔ)' THEN 1 ELSE NULL END) = 1
THEN '○' ELSE NULL END AS "UNIX基礎(chǔ)",
CASE WHEN SUM(CASE WHEN course = 'Java中級(jí)' THEN 1 ELSE NULL END) = 1
THEN '○' ELSE NULL END AS "Java中級(jí)"
FROM Courses
GROUP BY name;
2.做乘法運(yùn)算的連接
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;
問題：臨時(shí)視圖 SH 的數(shù)據(jù)需要臨時(shí)存儲(chǔ)在內(nèi)存里，還有就是雖然通過聚合將 item_no 變成了主鍵饮六，但是 SH 上卻不存在主鍵索引其垄，因此我們也就無法利用索引優(yōu)化查詢。
另一種通過先連接然后在聚合喜滨，沒有使用臨時(shí)視圖捉捅，會(huì)走索引。效率高虽风，代碼簡(jiǎn)潔寫。
SELECT Items.item_no, CASE WHEN SUM(quantity) IS NULL THEN 0 ELSE SUM(quantity) END totaly_qty FROM Items LEFT JOIN SalesHistory ON
Items.item_no = SalesHistory.item_no GROUP BY Items.item_no;
