SQL通常在不同的表或者视图间进行连接运算,但是也可以对相同的表进行“自连接”运算。自连接的处理过程不太容易想象,但是,一旦掌握自连接技术,便能快速解决很多问题。
一般使用自连接可以解决的问题使用子查询也可以解决,但是与子查询相比,自连接的表达性更强,更加便于理解SQL语句的意义。下面是一些自连接的常用场景。
排列:有顺序的有序对 <1,2> != <2,1>
组合:无顺序的无序对 {1,2} = {2,1}
这里有一张存放了商品名称及价格的表:
可以使用自连接分别查询出数据的可重排列、排列、组合
(1)可重排列
SELECT
P1. name AS name_1,
P2. name AS name_2
FROM
Products P2,
Products P1;
(2)排列
SELECT
P1. name AS name_1,
P2. name AS name_2
FROM
Products P2,
Products P1
WHERE
P1.name <> P2.name;
(3)组合
SELECT
P1. name AS name_1,
P2. name AS name_2
FROM
Products P2,
Products P1
WHERE
P1.name > P2.name;
从下面这张商品表中找出价格相等的商品的组合:
(1)使用自连接
SELECT DISTINCT
p1. NAME,
p1.price
FROM
Products2 p1,
Products2 p2
WHERE
p1.price = p2.price
AND p1. NAME <> p2. NAME;
需要注意的是,使用自连接查询必须加上 DISTINCT
,因为要找出的是价格相等的商品的组合,不加上 DISTINCT
查询的是排列,结果会出现重复行。
(2)使用子查询
SELECT
p1. NAME,
p1.price
FROM
Products2 p1
WHERE
EXISTS (
SELECT
*
FROM
Products2 p2
WHERE
p1.price = p2.price
AND p1. NAME <> p2. NAME
);
我们经常会遇到按照数值进行排序并且展示排序位次的需求,虽然使用RANK()函数可以解决,但是并不是通用方案,因为不是所有的数据库都支持RANK()函数。
(1)使用子查询
SELECT
p1. NAME,
p1.price,
(
SELECT
COUNT(p2.price)
FROM
Products2 p2
WHERE
p2.price > p1.price
) + 1 AS rank
FROM
Products2 p1
ORDER BY
rank;
如果要是排序rank保持连续,则 COUNT(p2.price)
改为 COUNT(DISTINCT p2.price)
即可
SELECT
p1. NAME,
p1.price,
(
SELECT
COUNT(DISTINCT p2.price)
FROM
Products2 p2
WHERE
p2.price > p1.price
) + 1 AS rank
FROM
Products2 p1
ORDER BY
rank;
(2)使用自连接
SELECT
p1. NAME,
p1.price,
COUNT(p2.price) + 1 AS rank
FROM
Products2 p1
LEFT JOIN Products2 p2 ON p1.price < p2.price
GROUP BY
p1. NAME
ORDER BY
rank;
我们可以通过分解sql语句看得更清楚,NAME1列是比NAME列价格贵的水果,没有比橘子、草莓、香蕉更贵的水果,所以这三种水果并列第1,其余水果的排名同理。
应该注意,此处自连接使用的是外连接。因为如果使用内连接,排名第一 的水果便不会出现在结果中,会被 p1.price<p2.price
条件排除在外。
需要注意的是,与普通连接相比,自连接的性能开销更大。用于自连接的列推荐使用主键或者在相关列上建立索引,其他表连接方式也是如此。