首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >自连接的妙用

自连接的妙用

作者头像
撸码那些事
发布2019-03-04 15:08:25
1.7K0
发布2019-03-04 15:08:25
举报
文章被收录于专栏:撸码那些事撸码那些事

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


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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2018-12-28,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 CoderFocus 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 可重排列、排列、组合
  • 查找局部不一致的列
  • 排序
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档