SQL进阶--自连接

卡迪尔直积：

-- 创建product表
drop table if exists products;
create table products(
name1 varchar(20),
price int8);
insert into products(name1, price) values('苹果', 50);
insert into products(name1, price) values('橙子', 100);
insert into products(name1, price) values('香蕉', 80);

-- 卡迪尔直积 -->>> 去重
select b.name1 name_1, a.name1 name_2
from products a, products b
where a.name1 <> b.name1;

结果：

-- 卡迪尔直积 -->>> 去仅仅调换了顺序的 所有不重复的排列组合
select b.name1 name_1, a.name1 name_2
from products a, products b
where a.name1 > b.name1;

结果：

select b.name1 name_1, a.name1 name_2, c.name1 name_3
from products a, products b, products c
where a.name1 > b.name1 and b.name1 > c.name1;

结果：

删除重复行

-- 在Oracle 中
-- 使用极值函数 rowid 
DELETE FROM products p1
    where rowid < (select max(p2.rowid)
                    from  products p2
                    where p1.name1 = p2.name1
                          and p1.price = p2.price);

排序

drop table if exists products;
create table products(
name1 varchar(20),
price int8
);
insert into  products(name1, price) values('苹果', 50);
insert into  products(name1, price) values('橘子', 100);
insert into  products(name1, price) values('葡萄', 50);
insert into  products(name1, price) values('西瓜', 80);
insert into  products(name1, price) values('柠檬', 30);
insert into  products(name1, price) values('草莓', 100);
insert into  products(name1, price) values('香蕉', 100);

-- 排序  DB2 ORACLE
select name1, price,
       rank() over (order by price desc) as rank_1,
       dense_rank() over (order by price desc) as rank_2
from products

-- 用非等值方法实现排序 mysql，同名的跳过重复排名
select p1.name1,
       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;

结果：

-- 用非等值方法实现排序 mysql  同名的不跳过重复排名
select p1.name1,
       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;

结果：

练习

 -- 1-2-1 可重复组合
select p1.name1 name_1, p2.name1 name_2
from products p1, products p2
where p1.name1 > p2.name1    -- 找到所有组合
      or p1.name1 = p2.name1 -- 允许自己和自己的组合存在
order by name_1;

-- 分地区排序
drop table if exists districtproducts;
create table districtproducts(
district varchar(20),
name1 varchar(20),
price int8
);
insert into districtproducts(district, name1, price) values('东北','橘子',100);
insert into districtproducts(district, name1, price) values('东北','苹果',50);
insert into districtproducts(district, name1, price) values('东北','葡萄',50);
insert into districtproducts(district, name1, price) values('东北','柠檬',30);
insert into districtproducts(district, name1, price) values('关东','柠檬',100);
insert into districtproducts(district, name1, price) values('关东','菠萝',100);
insert into districtproducts(district, name1, price) values('关东','苹果',100);
insert into districtproducts(district, name1, price) values('关东','葡萄',70);
insert into districtproducts(district, name1, price) values('关西','柠檬',70);
insert into districtproducts(district, name1, price) values('关西','西瓜',30);
insert into districtproducts(district, name1, price) values('关西','苹果',20);
-- 分区域 按照价格高低进行排序
select a.district, a.name1, a.price, 
       (select count(b.price)
        from districtproducts b
        where b.price > a.price  and b.district = a.district
        ) +1 rank_1
from districtproducts a 
order by district;

结果：

内容多来自 《SQL进阶教材》，仅做笔记。习题代码均为原创。