自连接的妙用

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条件排除在外。


需要注意的是,与普通连接相比,自连接的性能开销更大。用于自连接的列推荐使用主键或者在相关列上建立索引,其他表连接方式也是如此。

原文发布于微信公众号 - CoderFocus(lumanxs)

原文发表时间:2018-12-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券