SQL进阶之自连接的用法

自连接越前须知（雾）具体用法可重排列、排列、组合查询局部不一致的记录排序不分组排序分组排序

自连接

越前须知（雾）

本系列参考《SQL进阶教程》¹，DBMS选用MySQL。本系列不涉及数据库安装与基础语句，对初学者存在一定门槛；基础知识建议阅读《SQL必知必会（第四版）》与《SQL基础教程（第二版）》。

具体用法

可重排列、排列、组合

创表 CREATE TABLE Products (name VARCHAR(16) PRIMARY KEY, price INTEGER NOT NULL); ​ INSERT INTO Products VALUES('苹果', 50); INSERT INTO Products VALUES('橘子', 100); INSERT INTO Products VALUES('香蕉', 80); 可重排列 Q：允许 “（苹果，苹果）”组合 A：自连接，无约束 select P1.name, P2.name from Products P1, Products P2; 排列 Q：不允许“（苹果，苹果）”组合，但“（苹果，橘子）”和“（橘子，苹果）”是不一样的组合 A：P1.name <> P2.name select P1.name, P2.name from Products P1, Products P2 where P1.name <> P2.name 组合 Q：不允许“（苹果，苹果）”组合，且“（苹果，橘子）”和“（橘子，苹果）”是一样的组合 A：P1.name > P2.name -- 两个表自连接 select P1.name, P2.name from Products P1, Products P2 where P1.name > P2.name; -- 三个表自连接 select P1.name, P2.name, P3.name from Products P1, Products P2, Products P3 where P1.name > P2.name and P2.name > P3.name;

查询局部不一致的记录

创表 set sql_safe_updates = 0; DELETE FROM Products; -- 水果，示例1 INSERT INTO Products VALUES('苹果', 50); INSERT INTO Products VALUES('橘子', 100); INSERT INTO Products VALUES('葡萄', 50); INSERT INTO Products VALUES('西瓜', 80); INSERT INTO Products VALUES('柠檬', 30); INSERT INTO Products VALUES('香蕉', 50); CREATE TABLE Addresses -- 地址，示例2 (name VARCHAR(32), family_id INTEGER, address VARCHAR(32), PRIMARY KEY(name, family_id)); ​ INSERT INTO Addresses VALUES('前田义明', '100', '东京都港区虎之门3-2-29'); INSERT INTO Addresses VALUES('前田由美', '100', '东京都港区虎之门3-2-92'); INSERT INTO Addresses VALUES('加藤茶', '200', '东京都新宿区西新宿2-8-1'); INSERT INTO Addresses VALUES('加藤胜', '200', '东京都新宿区西新宿2-8-1'); INSERT INTO Addresses VALUES('福尔摩斯', '300', '贝克街221B'); INSERT INTO Addresses VALUES('华生', '400', '贝克街221B');

Q：查询相同价格的不同产品 A：自连接，p1.name <> p2.name and p1.price = p2.price

select distinct p1.name, p1.price from Products as p1, Products as p2 where p1.price = p2.price and p1.name <> p2.name;

Q：查询同一家庭、不同地址的记录 A：自连接，a1.family = a2.family and a1.address = a2.address

select a1.family_id, a1.address from Addresses as a1, Addresses as a2 where a1.family_id = a2.family_id and a1.address <> a2.address;

排序

不分组排序

Q：对水果按价格降序排序 A：窗口函数、自连接、外连接

窗口函数 RANK()：出现相同位次后，跳过之后位次DENSE_RANK()：出现相同位次后，不跳过之后位次ROW_NUMBER()：不出现相同位次 select name, price, rank() over (order by price desc) as rank1, dense_rank() over (order by price desc) as rank2, row_number() over (order by price desc) as rank3 from Products; 自连接 RANK()：count(p2.price) +1DENSE_RANK()：count(distinct p2.price) +1 select p1.name, p1.price, (select count(p2.price) from Products p2 -- rank() where p1.price < p2.price) +1 as rank_1, -- 子句中不需要group by (select count(distinct p2.price) + 1 from Products p2 -- dense_rank() where p1.price < p2.price) as rank_2 from Products p1 order by rank_1; 外连接 select p1.name, max(p1.price) as price, -- 使得不需要按p1.price分组 count(p2.price) + 1 as rank_1 from Products as p1 left outer join Products as p2 on p1.price < p2.price -- on p1.price < p2.price group by p1.name order by rank_1; 内连接的缺陷：第一名被 p1.price < p2.price 过滤 select p1.name, max(p1.price), count(p2.price)+1 as rank_1 from Products as p1 inner join Products as p2 on p1.price < p2.price group by p1.name order by rank_1;

分组排序

创表 CREATE TABLE DistrictProducts (district VARCHAR(16) NOT NULL, name VARCHAR(16) NOT NULL, price INTEGER NOT NULL, PRIMARY KEY(district, name, price)); ​ INSERT INTO DistrictProducts VALUES('东北', '橘子', 100); INSERT INTO DistrictProducts VALUES('东北', '苹果', 50); INSERT INTO DistrictProducts VALUES('东北', '葡萄', 50); INSERT INTO DistrictProducts VALUES('东北', '柠檬', 30); INSERT INTO DistrictProducts VALUES('关东', '柠檬', 100); INSERT INTO DistrictProducts VALUES('关东', '菠萝', 100); INSERT INTO DistrictProducts VALUES('关东', '苹果', 100); INSERT INTO DistrictProducts VALUES('关东', '葡萄', 70); INSERT INTO DistrictProducts VALUES('关西', '柠檬', 70); INSERT INTO DistrictProducts VALUES('关西', '西瓜', 30); INSERT INTO DistrictProducts VALUES('关西', '苹果', 20); -- 更新位次用表 CREATE TABLE DistrictProducts2 (district VARCHAR(16) NOT NULL, name VARCHAR(16) NOT NULL, price INTEGER NOT NULL, ranking INTEGER, PRIMARY KEY(district, name)); ​ INSERT INTO DistrictProducts2 VALUES('东北', '橘子', 100, NULL); INSERT INTO DistrictProducts2 VALUES('东北', '苹果', 50 , NULL); INSERT INTO DistrictProducts2 VALUES('东北', '葡萄', 50 , NULL); INSERT INTO DistrictProducts2 VALUES('东北', '柠檬', 30 , NULL); INSERT INTO DistrictProducts2 VALUES('关东', '柠檬', 100, NULL); INSERT INTO DistrictProducts2 VALUES('关东', '菠萝', 100, NULL); INSERT INTO DistrictProducts2 VALUES('关东', '苹果', 100, NULL); INSERT INTO DistrictProducts2 VALUES('关东', '葡萄', 70 , NULL); INSERT INTO DistrictProducts2 VALUES('关西', '柠檬', 70 , NULL); INSERT INTO DistrictProducts2 VALUES('关西', '西瓜', 30 , NULL); INSERT INTO DistrictProducts2 VALUES('关西', '苹果', 20 , NULL);

Q：将水果按不同地区分组，并在组内按价格降序排序 A：窗口函数、自连接、外连接

窗口函数 partition by select district, name, price, rank() over (partition by district order by price desc) as rank_1, dense_rank() over (partition by district order by price desc) as rank_2, row_number() over (partition by district order by price desc) as rank_3 from DistrictProducts; 自连接 select d1.district, d1.name, d1.price as price, (select count(d2.price)+1 from DistrictProducts d2 -- rank() where d1.district = d2.district and d1.price < d2.price) as rank_1, (select count(distinct d3.price)+1 from DistrictProducts d3 -- dense_rank() where d1.district = d3.district and d1.price < d3.price) as rank_2 from DistrictProducts as d1 order by district, rank_1, rank_2; 外连接 select d1.district, d1.name, max(d1.price) as price, count(d2.price)+1 as rank_1 from DistrictProducts d1 left outer join DistrictProducts d2 on d1.price < d2.price and d1.district = d2.district -- 连接条件 group by d1.district, d1.name -- 分组计数 order by d1.district, rank_1;

Q：更新 DistrictProducts2 表的 ranking 列 A：update语句+自连接

自连接 update DistrictProducts2 d1 set ranking = (select count(d2.price)+1 from DistrictProducts2 d2 where d1.district = d2.district and d1.price < d2.price); -- 分组 关联子查询+rank() update DistrictProducts2 set ranking = (select p1.ranking from (select district, name, rank() over (partition by district order by price desc) as ranking from DistrictProducts2) as p1 -- 创建临时视图 where p1.district = DistrictProducts2.district -- 分组匹配 and p1.name = DistrictProducts2.name);

---

MICK[日] 《SQL进阶教程》 ↩︎