SQL技巧之自连接的用法

参考书籍 : SQl进阶教程

排列, 组合

建表如下 :  products (name 商品名称, price 价格)

使用自连接SQL:

select p1.name name_1,p2.name name_2  
from products p1 , products p2

得到如下结果:

假如我们想要去除由相同元素组成的结果比如: <苹果, 苹果> <香蕉, 香蕉><橘子, 橘子>

SQL如下 :

select p1.name name_1,p2.name name_2  
from products p1 , products p2
where p1.name <> p2.name

得到结果如下 :

再假如我们把 <苹果, 香蕉> 和 <香蕉, 苹果> 这种元素视为相同元素, 又该如何去重呢?

SQL如下 :

select p1.name name_1,p2.name name_2  
from products p1 , products p2
where p1.name > p2.name

得到结果如下 :

注意 :  上面的两种方式在数学上称为"排列", "组合" .  另外, ">" 和 "<" 等比较运算符不仅可以用于比较数值大小, 也可以用于比较字符串(比如按字典进行比较) 或者日期等.

删除重复行

建表如下 :  

在这张表中没有主键,  表中橘子这种商品重复了, 我们需要清理

SQL如下 :

delete from products2 p1
 where rowid < (select max(p2.rowid)
                  from products2 p2
                 where p1.name = p2.name
                   and p1.price = p2.price)

或者 : 

 delete from products2 p1
  where exists (select 1
           from products2 p2
          where p1.name = p2.name
            and p1.price = p2.price
            and p1.rowid < p2.rowid)

得到如下结果 : 

注意 :  本例中是没有主键的情况, 使用了rowid (Oracle) 作为主键

查找局部不一致的列

建表如下 : 

要求 : 在本表中, 同一家人应该住在同一个地方, 但也有不是一家人却主在一起的情况, 

          表中因为填写出错, 导致一家人没有住在一起(前两行) , 现在要求找出所有这样的数据.

select a1.name, a1.family_id, a1.address 
from addresses a1 , addresses a2 
where a1.family_id = a2.family_id
  and a1.address <> a2.address

得到结果如下 : 

例2 建表如下 :

要求 : 找到价格相等的商品组合

SQL如下 : 

select distinct p1.name , p1.price
from products2 p1 ,products2 p2
where p1.price = p2.price
  and p1.name <> p2.name
order by p1.price  

得到结果如下 : 

注意 : 得到的结果必须使用 distincts 去重, 也可使用关联子查询改写 

SQL 如下 :

select p1.name, p1.price
  from products2 p1
 where exists (select 1
          from products2 p2
         where p1.name <> p2.name
           and p1.price = p2.price)
 order by p1.price 

排序

有表如下 :

现在要求 按价格排序由高到低, 价格相同位次一样. 它们之后的商品有两种排序方法 : 一种跳过之后的位次, 一种不跳过.

SQL如下(使用窗口函数) :

select name,
       price,
       rank() over(order by price desc) as rank_1,
       dense_rank() over(order by price desc) as rank_2
from products2 

结果如下 : 

不使用窗口函数SQL (标准SQL) 如下:

select name,
       price,
       (select count(p2.price) from products2 p2 where p2.price >p1.price) + 1 as rank_1,
       (select count(distinct p2.price) from products2 p2 where p2.price > p1.price) + 1 as rank_2
from products2 p1       
order by rank_1

 结果如下 :