SQL高级语法
— SQL, MySQL, Oracle, PostgreSQL — 2 min read
递归
MySQL
MySQL在8.0版本支持了RECURSIVE可以用于递归查询
生成5行数据
1WITH RECURSIVE cte (n) AS (2 SELECT 13 UNION ALL4 SELECT n + 15 FROM cte6 WHERE n < 57)8SELECT9 *10FROM cte;
执行结果
n |
---|
1 |
2 |
3 |
4 |
5 |
生成最近30天的日期
1WITH RECURSIVE cte (`date`) AS (2 SELECT current_date as `date`3 UNION ALL4 SELECT DATE_ADD(`date`, INTERVAL -1 DAY)5 FROM cte6 WHERE `date` > DATE_ADD(current_date, INTERVAL -29 DAY)7)8SELECT9 *10FROM cte;
执行结果
date |
---|
2024-01-23 |
2024-01-22 |
2024-01-21 |
2024-01-20 |
2024-01-19 |
…… |
2023-12-25 |
字符串转为临时表
逗号分 隔的字符串转为临时表
1SET @str = '2024-01-23,2024-01-22,2024-01-21,2024-01-20';2SET @total = LENGTH(@str) - LENGTH(REPLACE(@str, ',', '')) + 1;3WITH RECURSIVE cte AS (4 SELECT 1 AS num5 UNION ALL6 SELECT num + 17 FROM cte8 WHERE num < @total9)10SELECT11 SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', num), ',', -1) AS `date`12 FROM cte
执行结果
date |
---|
2024-01-23 |
2024-01-22 |
2024-01-21 |
2024-01-20 |
递归查询
- 数据准备
1CREATE TABLE employees (2 id INT PRIMARY KEY NOT NULL,3 name VARCHAR(100) NOT NULL,4 manager_id INT NULL,5 INDEX (manager_id),6 FOREIGN KEY (manager_id) REFERENCES employees (id)7);8
9INSERT INTO employees VALUES10(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)11(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)12(692, "Tarek", 333),13(29, "Pedro", 198),14(4610, "Sarah", 29),15(72, "Pierre", 29),16(123, "Adil", 692);
- 递归查询
1WITH RECURSIVE employee_paths (id, name, path) AS (2 SELECT3 id,4 name,5 CAST(id AS CHAR(200))6 FROM7 employees8 WHERE9 manager_id IS NULL10 UNION ALL11 SELECT12 e.id,13 e.name,14 CONCAT(ep.path, ',', e.id)15 FROM16 employee_paths AS ep17 JOIN employees AS e18 ON ep.id = e.manager_id19)20SELECT21 *22FROM23 employee_paths24ORDER BY path;
执行结果
id | name | path |
---|---|---|
333 | Yasmina | 333 |
198 | John | 333,198 |
29 | Pedro | 333,198,29 |
4610 | Sarah | 333,198,29,4610 |
72 | Pierre | 333,198,29,72 |
692 | Tarek | 333,692 |
123 | Adil | 333,692,123 |