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);

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

海上钢琴师_1900

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值