关于MySQL8的WITH查询学习

前言
示例
练习
总结

前言

对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性

MySQL 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。

可以查看官方文档【点击跳转】

示例

官方第一个示例，可以看出该查询语句创建了cte1,cte2,cte3,cte4这4个临时表，后面的临时表依赖前面的临时表数据。

最后一行为最终查询结果，实际ct4因为ct3结果包含3行数据，但是使用MAX,MIN得到一行结果。

WITH cte1(txt) AS (SELECT "This "),

     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),

     cte3(txt) AS (SELECT "nice query" UNION

                   SELECT "query that rocks" UNION

                   SELECT "query"),

     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)

SELECT MAX(txt), MIN(txt) FROM cte4;

+----------------------------+----------------------+

| MAX(txt)                   | MIN(txt)             |

+----------------------------+----------------------+

| This is a query that rocks | This is a nice query |

+----------------------------+----------------------+

1 row in set (0,00 sec)

官方第二个示例是递归的用法，根据阅读文档，我分析下面查询结果如下。

首先定义一个临时表my_cte

分析SELECT 1 AS n，这个是决定临时表的列名为n，值为1

然后SELECT 1+n FROM my_cte WHERE n<10，这个是递归查询n<10,并将1+n作为结果填充临时表

最终使用SELECT * FROM my_cte，查询临时表，因此查询出的结果就显而易见了

WITH RECURSIVE my_cte AS

(

  SELECT 1 AS n

  UNION ALL

  SELECT 1+n FROM my_cte WHERE n<10

)

SELECT * FROM my_cte;

+------+

| n    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

|   10 |

+------+

10 rows in set (0,00 sec)

根据我的理解写了如下2个不一样的查询，查询结果都一样。

值得注意的是临时表里面的多个查询列数量和类型必须一样，不然会报错。

这个是将临时表列名指定在第一行

WITH RECURSIVE my_cte(a,b,c) AS

(

  SELECT 1,1,1

  UNION ALL

  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10

)

SELECT * FROM my_cte;

这个第一行没有指定列名，然后列名由第一个查询返回结果确定

WITH RECURSIVE my_cte AS

(

  SELECT 1 AS a,1 AS b,1 AS c

  UNION ALL

  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10

)

SELECT * FROM my_cte;

根据官方文档，临时表的语法模板如下，是可以有很多行的查询共同组成。

WITH RECURSIVE cte_name [list of column names ] AS

(

  SELECT ...      <-- specifies initial set

  UNION ALL

  SELECT ...      <-- specifies initial set

  UNION ALL

  ...

  SELECT ...      <-- specifies how to derive new rows

  UNION ALL

  SELECT ...      <-- specifies how to derive new rows

  ...

)

[, any number of other CTE definitions ]

官方文档还列出了，使用临时表时可以增删改查新表，具体可以去阅读官方文档。

练习

关于递归的练习主要用于表里面包含父节点id之类的，详情可以参考下面的练习。

定义下面这样的表，存储每个区域（省、市、区）的id，名字及上级区域的pid



CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));

INSERT INTO tb VALUES('002', 0, '浙江省');

INSERT INTO tb VALUES('001', 0, '广东省');

INSERT INTO tb VALUES('003', '002', '衢州市');

INSERT INTO tb VALUES('004', '002', '杭州市');

INSERT INTO tb VALUES('005', '002', '湖州市');

INSERT INTO tb VALUES('006', '002', '嘉兴市');

INSERT INTO tb VALUES('007', '002', '宁波市');

INSERT INTO tb VALUES('008', '002', '绍兴市');

INSERT INTO tb VALUES('009', '002', '台州市');

INSERT INTO tb VALUES('010', '002', '温州市');

INSERT INTO tb VALUES('011', '002', '丽水市');

INSERT INTO tb VALUES('012', '002', '金华市');

INSERT INTO tb VALUES('013', '002', '舟山市');

INSERT INTO tb VALUES('014', '004', '上城区');

INSERT INTO tb VALUES('015', '004', '下城区');

INSERT INTO tb VALUES('016', '004', '拱墅区');

INSERT INTO tb VALUES('017', '004', '余杭区');

INSERT INTO tb VALUES('018', '011', '金东区');

INSERT INTO tb VALUES('019', '001', '广州市');

INSERT INTO tb VALUES('020', '001', '深圳市');

WITH RECURSIVE cte AS (

	SELECT id,name FROM tb WHERE id='002'

	UNION ALL

	SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid

) SELECT * FROM cte;

执行结果：

分析结果包含第一行SELECT id,name FROM tb WHERE id='002'的数据，此时表中只有一行数据

然后连表查询SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid，递归的将父节点数据放入临时表

最终查询出来的就是递归的结果。

总结

通过阅读官方文档，我知道了WITH查询是为了避免出现嵌套的子查询，每个查询结果都可以是一个临时表，然后总查询可以用到所有临时表的数据。

然后就是递归查询，可以解决树形接口的情况，数据有父子层级的那种。

巴特西

关于MySQL8的WITH查询学习

前言

示例

练习

总结

最新文章

热门文章