SQL进阶_2

自连接

针对相同的表进行的连接被称为“自连接”（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);