具体用法

可重排列、排列、组合

CREATE TABLE Products(name VARCHAR(16) PRIMARY KEY,price INTEGER NOT NULL);​INSERT INTO Products VALUES('苹果', 50);INSERT INTO Products VALUES('橘子', 100);INSERT INTO Products VALUES('香蕉', 80);

可重排列

Q：允许 “（苹果，苹果）”组合

A：自连接，无约束

select P1.name, P2.name from Products P1, Products P2;

排列

Q：不允许“（苹果，苹果）”组合，但“（苹果，橘子）”和“（橘子，苹果）”是不一样的组合

A：P1.name <> P2.name

select P1.name, P2.namefrom Products P1, Products P2where P1.name <> P2.name

组合

Q：不允许“（苹果，苹果）”组合，且“（苹果，橘子）”和“（橘子，苹果）”是一样的组合

A：P1.name > P2.name

-- 两个表自连接select P1.name, P2.name from Products P1, Products P2 where P1.name > P2.name;-- 三个表自连接select P1.name, P2.name, P3.name from Products P1, Products P2, Products P3 where P1.name > P2.name and P2.name > P3.name;

查询局部不一致的记录

set sql_safe_updates = 0;DELETE FROM Products; -- 水果，示例1INSERT INTO Products VALUES('苹果', 50);INSERT INTO Products VALUES('橘子', 100);INSERT INTO Products VALUES('葡萄', 50);INSERT INTO Products VALUES('西瓜', 80);INSERT INTO Products VALUES('柠檬', 30);INSERT INTO Products VALUES('香蕉', 50);CREATE TABLE Addresses -- 地址，示例2(name VARCHAR(32),family_id INTEGER,address VARCHAR(32),PRIMARY KEY(name, family_id));​INSERT INTO Addresses VALUES('前田义明', '100', '东京都港区虎之门3-2-29');INSERT INTO Addresses VALUES('前田由美', '100', '东京都港区虎之门3-2-92');INSERT INTO Addresses VALUES('加藤茶', '200', '东京都新宿区西新宿2-8-1');INSERT INTO Addresses VALUES('加藤胜', '200', '东京都新宿区西新宿2-8-1');INSERT INTO Addresses VALUES('福尔摩斯', '300', '贝克街221B');INSERT INTO Addresses VALUES('华生','400', '贝克街221B');

Q：查询相同价格的不同产品

A：自连接，p1.name <> p2.name and p1.price = p2.price

select distinct p1.name, p1.price from Products as p1, Products as p2where p1.price = p2.price and p1.name <> p2.name;

Q：查询同一家庭、不同地址的记录

A：自连接，a1.family = a2.family and a1.address = a2.address

select a1.family_id, a1.address from Addresses as a1, Addresses as a2where a1.family_id = a2.family_id and a1.address <> a2.address;

排序

不分组排序

Q：对水果按价格降序排序

A：窗口函数、自连接、外连接

窗口函数 RANK()：出现相同位次后，跳过之后位次DENSE_RANK()：出现相同位次后，不跳过之后位次ROW_NUMBER()：不出现相同位次

select name, price, rank() over (order by price desc) as rank1, dense_rank() over (order by price desc) as rank2,row_number() over (order by price desc) as rank3from Products;

自连接 RANK()：count(p2.price) +1DENSE_RANK()：count(distinct p2.price) +1

select p1.name, p1.price, (select count(p2.price) from Products p2 -- rank()where p1.price < p2.price) +1 as rank_1, -- 子句中不需要group by(select count(distinct p2.price) + 1 from Products p2 -- dense_rank()where p1.price < p2.price) as rank_2from Products p1 order by rank_1;

外连接

select p1.name, max(p1.price) as price, -- 使得不需要按p1.price分组count(p2.price) + 1 as rank_1from Products as p1 left outer join Products as p2on p1.price < p2.price -- on p1.price < p2.pricegroup by p1.nameorder by rank_1;

内连接的缺陷：第一名被 p1.price < p2.price 过滤

select p1.name, max(p1.price), count(p2.price)+1 as rank_1from Products as p1 inner join Products as p2 on p1.price < p2.pricegroup by p1.nameorder by rank_1;

分组排序

CREATE TABLE DistrictProducts(district VARCHAR(16) NOT NULL,nameVARCHAR(16) NOT NULL,priceINTEGER NOT NULL,PRIMARY KEY(district, name, price));​INSERT INTO DistrictProducts VALUES('东北', '橘子', 100);INSERT INTO DistrictProducts VALUES('东北', '苹果', 50);INSERT INTO DistrictProducts VALUES('东北', '葡萄', 50);INSERT INTO DistrictProducts VALUES('东北', '柠檬', 30);INSERT INTO DistrictProducts VALUES('关东', '柠檬', 100);INSERT INTO DistrictProducts VALUES('关东', '菠萝', 100);INSERT INTO DistrictProducts VALUES('关东', '苹果', 100);INSERT INTO DistrictProducts VALUES('关东', '葡萄', 70);INSERT INTO DistrictProducts VALUES('关西', '柠檬', 70);INSERT INTO DistrictProducts VALUES('关西', '西瓜', 30);INSERT INTO DistrictProducts VALUES('关西', '苹果', 20);-- 更新位次用表CREATE TABLE DistrictProducts2(district VARCHAR(16) NOT NULL,nameVARCHAR(16) NOT NULL,priceINTEGER NOT NULL,ranking INTEGER,PRIMARY KEY(district, name));​INSERT INTO DistrictProducts2 VALUES('东北', '橘子', 100, NULL);INSERT INTO DistrictProducts2 VALUES('东北', '苹果', 50 , NULL);INSERT INTO DistrictProducts2 VALUES('东北', '葡萄', 50 , NULL);INSERT INTO DistrictProducts2 VALUES('东北', '柠檬', 30 , NULL);INSERT INTO DistrictProducts2 VALUES('关东', '柠檬', 100, NULL);INSERT INTO DistrictProducts2 VALUES('关东', '菠萝', 100, NULL);INSERT INTO DistrictProducts2 VALUES('关东', '苹果', 100, NULL);INSERT INTO DistrictProducts2 VALUES('关东', '葡萄', 70 , NULL);INSERT INTO DistrictProducts2 VALUES('关西', '柠檬', 70 , NULL);INSERT INTO DistrictProducts2 VALUES('关西', '西瓜', 30 , NULL);INSERT INTO DistrictProducts2 VALUES('关西', '苹果', 20 , NULL);

Q：将水果按不同地区分组，并在组内按价格降序排序

A：窗口函数、自连接、外连接

窗口函数 partition by

select district, name, price, rank() over (partition by district order by price desc) as rank_1,dense_rank() over (partition by district order by price desc) as rank_2,row_number() over (partition by district order by price desc) as rank_3from DistrictProducts;

自连接

select d1.district, d1.name, d1.price as price, (select count(d2.price)+1 from DistrictProducts d2 -- rank()where d1.district = d2.district and d1.price < d2.price) as rank_1,(select count(distinct d3.price)+1 from DistrictProducts d3 -- dense_rank()where d1.district = d3.district and d1.price < d3.price) as rank_2from DistrictProducts as d1order by district, rank_1, rank_2;

外连接

select d1.district, d1.name, max(d1.price) as price, count(d2.price)+1 as rank_1from DistrictProducts d1 left outer join DistrictProducts d2on d1.price < d2.price and d1.district = d2.district -- 连接条件group by d1.district, d1.name -- 分组计数order by d1.district, rank_1;

Q：更新 DistrictProducts2 表的 ranking 列

A：update语句+自连接

自连接

update DistrictProducts2 d1 set ranking = (select count(d2.price)+1 from DistrictProducts2 d2where d1.district = d2.district and d1.price < d2.price); -- 分组

关联子查询+rank()

update DistrictProducts2 set ranking = (select p1.ranking from (select district, name, rank() over (partition by district order by price desc) as ranking from DistrictProducts2) as p1 -- 创建临时视图where p1.district = DistrictProducts2.district -- 分组匹配and p1.name = DistrictProducts2.name);

MICK[日] 《SQL进阶教程》 ↩︎