【51摸鱼-7】MariaDB 中的 CTE 示例

原创严少安 2023-06-14

402

CTE

MariaDB中的CTE是公用表表达式。CTE是一个命名的临时结果集，仅在单个SQL语句（例如select、insert、delete和update）的执行范围内存在。

CTE是标准SQL的特性，属于表表达式的一种，MariaDB支持CTE，MySQL 8才开始支持CTE。CTE分为递归CTE和非递归CTE，它是一种可以在一个查询中多次使用的查询结果集，可以提高查询的可读性，提高查询的性能，并且可以解决循环查询的问题

mariadb中的CTE功能有以下2种：

递归查询：递归CTE可以极大地简化在SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句中运行递归查询所需的代码。它会不断地重复查询每一次递归得到的子集，直到得到最后的结果。
层次查询：递归CTE非常适合处理“树状结构”的数据或者有“层次关系”的数据。

演示示例

创建测试表

CREATE TABLE category(category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,parent INT DEFAULT NULL);

导入测试数据

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

具体演示如下：

WITH RECURSIVE cte AS(SELECT category_id as id, name, 0 AS depth, cast(category_id as char(20)) AS path FROM category WHERE parent IS NULLUNION ALLSELECT c.category_id, c.name, cte.depth+1, concat(cte.path, ",", c.category_id) FROM category c JOIN cte ONcte.id=c.parent)SELECT * FROM cte ORDER BY depth, path;

输出结果：

+------+----------------------+-------+---------+
| id   | name                 | depth | path    |
+------+----------------------+-------+---------+
|    1 | ELECTRONICS          |     0 | 1       |
|    2 | TELEVISIONS          |     1 | 1,2     |
|    6 | PORTABLE ELECTRONICS |     1 | 1,6     |
|    3 | TUBE                 |     2 | 1,2,3   |
|    4 | LCD                  |     2 | 1,2,4   |
|    5 | PLASMA               |     2 | 1,2,5   |
|   10 | 2 WAY RADIOS         |     2 | 1,6,10  |
|    7 | MP3 PLAYERS          |     2 | 1,6,7   |
|    9 | CD PLAYERS           |     2 | 1,6,9   |
|    8 | FLASH                |     3 | 1,6,7,8 |
+------+----------------------+-------+---------+
10 rows in set (0.001 sec)

递归执行过程如下：

查找parent IS NULL的第一种类别，我们可以得到ELECTRONICS
接着查找parent == ELECTRONICS的第二类电器种类，可以看出我们可以得到TELEVISIONS和PORTABLE ELECTRONICS
接着查找parent == TELEVISIONS 和 parent == PORTABLE ELECTRONICS，我们可以得到第三类电器分别是PLASMA，MP3 PLAYERS，CD PLAYERS，2 WAY RADIOS，TUBE，LCD
接着继续查找属于第三类电器种类的产品，最后得到 FLASH。
执行完毕。