SQL 自连接的魅力
SQL 通常在不同的表或者视图间进行连接运算，包括 INNER JOIN、 LEFT JOIN、 RIGHT JOIN 或 CROSS JOIN，但是，有一种特殊情况，需要将一个表连接到它自己，这就是所谓的自连接。
自连接的处理过程比较抽象，难以想象、难以理解，但是，一旦掌握其用法，我们便能快速的处理很多问题。
下面来看一个微信群里提到的问题，如下完成下面的转换？
乍一看，好像 CASE WHEN 就可以了，其实不然，这里的 item 都是一样的，使用 CASE WHEN 只会得到一行数据。
这里本质上是要进行行与行之间的比较，将偶数行与奇数行合并，使用自连接再合适不过了，我们这里先将该表进行自连接（不加条件）。
mysql> SELECT * FROM A A1,A A2;
+------+-------+-------+------+-------+-------+
| item | type | time | item | type | time |
+------+-------+-------+------+-------+-------+
| A | start | 01:00 | A | start | 01:00 |
| A | end | 02:00 | A | start | 01:00 |
| A | start | 03:00 | A | start | 01:00 |
| A | end | 04:00 | A | start | 01:00 |
| A | start | 05:00 | A | start | 01:00 |
| A | end | 06:00 | A | start | 01:00 |
| A | start | 01:00 | A | end | 02:00 |
| A | end | 02:00 | A | end | 02:00 |
| A | start | 03:00 | A | end | 02:00 |
| A | end | 04:00 | A | end | 02:00 |
| A | start | 05:00 | A | end | 02:00 |
| A | end | 06:00 | A | end | 02:00 |
| A | start | 01:00 | A | start | 03:00 |
| A | end | 02:00 | A | start | 03:00 |
| A | start | 03:00 | A | start | 03:00 |
| A | end | 04:00 | A | start | 03:00 |
| A | start | 05:00 | A | start | 03:00 |
| A | end | 06:00 | A | start | 03:00 |
| A | start | 01:00 | A | end | 04:00 |
| A | end | 02:00 | A | end | 04:00 |
| A | start | 03:00 | A | end | 04:00 |
| A | end | 04:00 | A | end | 04:00 |
| A | start | 05:00 | A | end | 04:00 |
| A | end | 06:00 | A | end | 04:00 |
| A | start | 01:00 | A | start | 05:00 |
| A | end | 02:00 | A | start | 05:00 |
| A | start | 03:00 | A | start | 05:00 |
| A | end | 04:00 | A | start | 05:00 |
| A | start | 05:00 | A | start | 05:00 |
| A | end | 06:00 | A | start | 05:00 |
| A | start | 01:00 | A | end | 06:00 |
| A | end | 02:00 | A | end | 06:00 |
| A | start | 03:00 | A | end | 06:00 |
| A | end | 04:00 | A | end | 06:00 |
| A | start | 05:00 | A | end | 06:00 |
| A | end | 06:00 | A | end | 06:00 |
+------+-------+-------+------+-------+-------+
36 rows in set (0.00 sec)
这里得到了该表进行自连接的笛卡尔积（36行），根据查询的目标结果，这里需要过滤出 A1.type='start' AND A2.type='end'，即可得到如下结果：
mysql> SELECT * FROM A A1,A A2
-> WHERE A1.type='start' AND A2.type='end';
+------+-------+-------+------+------+-------+
| item | type | time | item | type | time |
+------+-------+-------+------+------+-------+
| A | start | 01:00 | A | end | 02:00 |
| A | start | 03:00 | A | end | 02:00 |
| A | start | 05:00 | A | end | 02:00 |
| A | start | 01:00 | A | end | 04:00 |
| A | start | 03:00 | A | end | 04:00 |
| A | start | 05:00 | A | end | 04:00 |
| A | start | 01:00 | A | end | 06:00 |
| A | start | 03:00 | A | end | 06:00 |
| A | start | 05:00 | A | end | 06:00 |
+------+-------+-------+------+------+-------+
9 rows in set (0.00 sec)
留下想要的列
mysql> SELECT A1.item,A1.time,A2.time FROM A A1,A A2
-> WHERE A1.type='start' AND A2.type='end';
+------+-------+-------+
| item | time | time |
+------+-------+-------+
| A | 01:00 | 02:00 | # 目标行 1
| A | 03:00 | 02:00 |
| A | 05:00 | 02:00 |
| A | 01:00 | 04:00 |
| A | 03:00 | 04:00 | # 目标行 2
| A | 05:00 | 04:00 |
| A | 01:00 | 06:00 |
| A | 03:00 | 06:00 |
| A | 05:00 | 06:00 | # 目标行 3
+------+-------+-------+
9 rows in set (0.00 sec)
已经非常接近目标结果了，上面输出结果的第 1、5、9 行就是我们想要的结果。
目标结果里，开始时间小于结果时间，这里留下 A1.time < A2.time 的行
mysql> SELECT A1.item,A1.time,A2.time FROM A A1,A A2
-> WHERE A1.type='start' AND A2.type='end'
-> AND A1.time < A2.time;
+------+-------+-------+
| item | time | time |
+------+-------+-------+
| A | 01:00 | 02:00 |
| A | 01:00 | 04:00 |
| A | 03:00 | 04:00 |
| A | 01:00 | 06:00 |
| A | 03:00 | 06:00 |
| A | 05:00 | 06:00 |
+------+-------+-------+
6 rows in set (0.00 sec)
通过观察可以发现，针对每一个 A1.item 和 A1.time 的组合，我们想要的就是 A2.time 的最小值。很自然就想到了聚合函数 MIN
mysql> SELECT A1.item,A1.time,MIN(A2.time) FROM A A1,A A2
-> WHERE A1.type='start' AND A2.type='end'
-> AND A1.time < A2.time
-> GROUP BY A1.item,A1.time;
+------+-------+--------------+
| item | time | MIN(A2.time) |
+------+-------+--------------+
| A | 01:00 | 02:00 |
| A | 03:00 | 04:00 |
| A | 05:00 | 06:00 |
+------+-------+--------------+
3 rows in set (0.00 sec)
最后，设置下别名
mysql> SELECT A1.item,A1.time AS startdate,MIN(A2.time) AS enddate FROM A A1,A A2
-> WHERE A1.type='start' AND A2.type='end'
-> AND A1.time < A2.time
-> GROUP BY A1.item,A1.time;
+------+-----------+---------+
| item | startdate | enddate |
+------+-----------+---------+
| A | 01:00 | 02:00 |
| A | 03:00 | 04:00 |
| A | 05:00 | 06:00 |
+------+-----------+---------+
3 rows in set (0.00 sec)
终于得到了目标结果。这就是自连接的一个应用，你 GET 到了吗？
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-01-06，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读