自连接的用法

可重排列、排列、组合

假设现在有这样一张表：

create table products
(
name varchar(10),
price int
)character set=utf8
;

insert into products values
('苹果',50),
('橘子',100),
('香蕉',80);

现在需要把该表中不同的商品进行组合，同一种组合只能出现一次。单纯的使用笛卡尔积可以得到全排列组合，里面会有(苹果，苹果)这样的相同元素组合，或者是(苹果，橘子)，(橘子，苹果)这样的组合相同但是顺序不同的元素组合。
为去除相同元素的组合，可以在限制条件中加上限制条件来去除相同元素。

SELECT P1.name AS name_1, P2.name AS name_2
  FROM products P1, products P2
 WHERE P1.name <> P2.name;

这样得到的结果就会去除掉相同元素的集合。更进一步的，如果要去除结果中的元素相同但顺序不同的数据，这时可以通过如下写法来完成。

SELECT P1.name AS name_1, P2.name AS name_2   
FROM products P1, products P2  
WHERE P1.name > P2.name;

这样得出的结果就都是完全唯一的了。
如果推广到两个及以上的组合则可以按照如下写法，以 3 个为例。

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;

在获取列的组合时，这种非等值自连接的做法非常常见，需要牢记。

删除重复行

删除重复行的方法很多，有些可以直接删，有些需要配合主键才能删，下面分别介绍一下。
例如表中现在有以下数据。

在有主键或唯一标识的情况下，可以通过如下写法来进行去重。

delete from products p1
where id>(select max(p2.id) from products p2 
          where p1.name=p2.name
          and p1.price=p2.price)

这里使用的是关联子查询，主查询中的每行数据都会到子查询中再查一次。例如第一行数据(苹果，50)进入到子查询中得到的最大 id 是 6 ，1<6 所以第一行数据就会被删除。按此逻辑上述条件只要不写等于都能实现删除重复行的功能。
此外上述语句也可以换一种写法使用 exists 来实现。

delete from products p1
where exists(select * from products p2 
            where p1.name=p2.name
            and p1.price=p2.price
            and p1.id>p2.id)

唯一标识也可以数据库内置的虚拟列，例如 oracle 的 rowid 和 postgresql 的 oid。
对于没有唯一键的表，可以通过如下写法来实现。

 create table products_copy as 
 select distinct name,price 
 from products;

这样可以将去重后的数据存入另一张表中，后续再覆盖掉原表就完成了去重操作。

查找局部不一致的列

如图所示表，当需要找出属于同一家庭但地址不同的条目时，可以使用如下写法。

SELECT  A1.name, A1.address
FROM Addresses A1, Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;

这样就可以得出如下结果：

这种写法可以得出一张表中局部一致的数据，但是但局部相同的数据量大于 2 条时，得出的数据会存在重复值，这时需要使用 distinct 来进行去重。
例如如下表，我们现在需要查找出价格相同但名称不同的条目.

如果使用之前的逻辑，可以写出如下 SQL。

SELECT  P1.name, P1.price
  FROM products P1, products P2
 WHERE P1.price = P2.price
   AND P1.name <> P2.name;

从 sql 运行结果可以看出其中存在重复数据。

这种时候就需要在前面加上 distinct 来进行去重。

SELECT  distinct P1.name, P1.price
  FROM products P1, products P2
 WHERE P1.price = P2.price
   AND P1.name <> P2.name;

如果使用关联子查询可以不使用 distinct，写法如下：

SELECT  P1.name, P1.price
  FROM products P1
 WHERE exists (select * from products P2
   where P1.price = P2.price
   and P1.name <> P2.name
   );

但如果表中本身就存在重复数据，那么使用子查询也会有重复数据。

排序

现在需要对如下表的数据按照价格进行排序，现在有两种排序方法，一种是跳过并列的排名一种是不跳过并列的排名。

对于这种需求一般情况下我们直接通过开窗函数就能直接实现。

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS rank_1,
       DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
  FROM products;

不过目前并不是所有数据库都实现了该函数，例如 MySQL 中目前还无法使用该函数，所以在 MySQL 中就需要使用其他的方法。

select P1.name,P1.price,
(select count(P2.price) from products P2
where P2.price>P1.price)+1 rank_1,
(select count(distinct P3.price) from products P3
where P3.price>P1.price)+1 rank_2
from products P1
order by rank_1;

这里需要重提一下子查询的知识点，主查询中的每一条数据都会执行一次子查询。例如对上面的 rank_1,主程序读到第一个数据苹果，然后会将其带入子查询中，得出有 4 个比苹果贵的水果，然后计算出名次为5，以此类推。
该子查询也可更改为自连接的形式

select P1.name,max(P1.price)price,
count(P2.price)+1 rank_1,
count(distinct P2.price)+1 rank_2
from products P1 left join products P2
on P1.price<P2.price
group by P1.name
order by rank_1;

注意该处使用的是左关联而不是内连接，如果使用内连接的话最贵的水果因为没有比它更贵的水果，那么在 P1.price<P2.price 限制条件下就会丢掉第一名的结果，示例如下：

练习

对于如下表，按地区进行排序

有两种写法，分别为子查询写法与自连接写法，窗口函数 mysql 无法实现就不写了

--子查询写法
select P1.district,P1.name,P1.price,
(select count(P2.price) from DistrictProducts P2
where P1.district=P2.district
and P1.price<P2.price)+1 rank_1
from DistrictProducts P1
order by P1.district,P1.name;

--自连接写法
select P1.district,P1.name,max(P1.price) price,
count(P2.price)+1 rank_1
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,P1.name;

以上两种写法得到的结果都是一致的。

如果现在在表 DistrictProducts 后新加一列 ranking，该列初始值为 null，然后需要动态的来更新该列，则可以按照如下写法。

update DistrictProducts P1
set ranking=(select count(P2.price)+1 from DistrictProducts P2
where P1.district=P2.district
and P1.price<P2.price);