数据库中的join操作不一定是两个不同的表，同一张表也可以自己连接自己，只是需要起个别名，合理的使用自连接可以完成一些巧妙的操作。
自连接
Products.png
1.实现商品的所有排列组合
直接用cross join就可以实现
image.png
- 去掉和自身重复的
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 CROSS JOIN Products P2
ON P1.`name` <> P2.`name`;
image.png
3.不区分排列组合的顺序
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 CROSS JOIN Products P2
ON P1.`name` < P2.`name`;
image.png
如果想获取三个以上元素可以基于该语句扩展，where后面依旧加相应条件。
- 查找重复行
如下图找出重复的水果保留row_id最大的，实际上不论id是字符串还是数字都可以用符号比较大小，row_id不一定是数值型。
image.png
这是关联子查询
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就可以实现的，应该把要删除的选出来放在一个中间表，然后再删除。
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.数据编号排序
需要按照价格排序.png
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;
也可以使用自连接的方式实现
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```
这里一定是要左连接，因为第一名在p1.price < p2.price 条件下不会出现在p1里面，必须使用COUNT(P2.price)不能用COUNT(*),如果需要不跳过价格相同的名词，只需要COUNT(DISTINCT p2.price) 就可以了。
排序.png
练习题：
image.png
因为是组合，所以（香蕉, 橘子）和（橘子, 香蕉）这样顺序相反的对被视为相同的对。此外，因为允许重复，所以结果里也出现了（橘子, 橘子）这样的对。
SELECT P1.NAME,P2.NAME FROM Products P1 JOIN Products P2
ON P1.`name` >= P2.`name`
DistrictProducts.png
要求结果.png
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.表格的格式转换
image.png
image.png
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;
或者用CASE在SELECT里面做选择，类似于前面提到的统计某个州县的男女数量一样。
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;
2.做乘法运算的连接
Items.png
SalesHistory.png
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 的数据需要临时存储在内存里，还有就是虽然通过聚合将 item_no 变成了主键，但是 SH 上却不存在主键索引，因此我们也就无法利用索引优化查询。
另一种通过先连接然后在聚合，没有使用临时视图，会走索引。效率高，代码简洁写。
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;
网友评论