自连接
针对相同的表进行的连接被称为“自连接”(self join)。
面向对象语言以对象的方式来描述世界,而面向集合语言 SQL 以集合的方式来描述世界。
自连接技术充分体现了 SQL 面向集合的特性。
特性
1、可重排列、排列、组合
组合:有顺序的有序对。 <1, 2> ≠ <2, 1>
组合:无顺序的无序对。 {1, 2} = {2, 1}
有序对的实现(笛卡尔积)
select p1.name as name_1, p2.name as name_2 from Products p1,Products p2;
无序对的实现
双列sql
select
p1.name as name_1, p2.name as name_2
from
Products p1,Products p2
where
p1.`name` > p2.`name`;
效果
三列sql
select
p1.name as name_1, p2.name as name_2, p3.name as name_3
from
Products p1,Products p2,Products p3
where
p1.`name` > p2.`name` and p2.`name` > p3.`name`;
效果
“>”和“<”等比较运算符不仅可以用于比较数值大小,也可以用于比较字符串(比如按字典序进行比较)或者日期等。
2、删除重复行
表数据
删除苹果这一重复数据,实现效果
oracle 实现方式
delete from
Products p1
where
rowid < (select max(rowid)
from Products p2
where p1.name = p2.name and p1.price = p2.price)
DELETE FROM Products P1
WHERE EXISTS ( SELECT *
FROM Products P2
WHERE P1.name = P2.name
AND P1.price = P2.price
AND P1.rowid < P2.rowid );
这个子查询会比较两个集合P1 和 P2,然后返回商品名称和价格都相同的行里最大的 rowid 所在的行。
于是,由于苹果和香蕉没有重复行,所以返回的行是“1:苹果”“5:香蕉”,而判断条件是不等号,所以该行不会被删除。而对于“橘子”这个商品,程序返回的行是“4:橘子”,那么 rowid 比 4 小的两行——“2:橘子”和“3:橘子”都会被删除。
mysql 实现方式
create table Products1 as (select distinct * from Products);
truncate table Products;
insert into Products (select * from Products1);
简单粗暴!
3、查找局部不一致的列
表数据
刘德华和朱丽倩地址应该是一样的,但是存在误输入的情况,就变成上述那样?
查找局部不一样的列的sql
select
a1.*
from
Addresses a1, Addresses a2
where
a1.family_id = a2.family_id and a1.address <> a2.address;
效果
4、排序
表数据
效果
价格一样的并列,价格不一样的不出现‘断层’
实现sql
方式1
select
p1.name,
p1.price,
(select count(distinct p2.price) from Products p2 where p2.price > p1.price) + 1 as rank_1
from
Products p1
order by
rank_1;
方式2
select
p1.name,
max(p1.price) as price,
(count(p2.name) + 1) as rank_1
from
Products p1
left join
Products p2
on
p1.price < p2.price
group by
p1.name
order by
rank_1;
效果
出现‘断层’(去掉distinct 便可达到效果)
sql
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;
效果
有一个需要注意的地方,与多表之间进行的普通连接相比,自连接的性能开销更大(特别是与非等值连接结合使用的时候),因此用于自连接的列推荐使用主键或者在相关列上建立索引。
要点!!!
1. 自连接经常和非等值连接结合起来使用。
2. 自连接和 GROUP BY 结合使用可以生成递归集合。
3. 将自连接看作不同表之间的连接更容易理解。
4. 应把表看作行的集合,用面向集合的方法来思考。
5. 自连接的性能开销更大,应尽量给用于连接的列建立索引。
练习
需求1:可重组合
表数据
实现效果
实现sql
select
p2.name p1_name,p1.name p2_name
from
Products p1,Products p2
where
p2.name >= p1.`name`
order by
p1_name desc;
需求2:分地区排序
把上表中的数据,按成以下方式进行排序(分组后组内小排序)
实现的效果
实现sql
select
d1.district,
d1.name,
d1.price,
(select count(d2.name) from DistrictProducts d2 where d2.district = d1.district and d2.price > d1.price) + 1 as rank_1
from
DistrictProducts d1
order by
d1.district,rank_1;
(select count(d2.name) from DistrictProducts d2 where d2.district = d1.district and d2.price > d1.price)
表d1 和表d2 的省份相同是先提条件,两表的价格差来决定比当前水果价格小的数量。
需求3:更新位次
原始数据
需要把ranking 列进行填充
实现效果
sql
update DistrictProducts2 d1 set ranking =
(select count(d2.name) + 1 from DistrictProducts d2 where d2.district = d1.district and d2.price < d1.price);