SQL高级语法

递归

MySQL

MySQL在8.0版本支持了RECURSIVE可以用于递归查询

生成5行数据

WITH RECURSIVE cte (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n < 5
)
SELECT
    *
FROM cte;

执行结果

n
1
2
3
4
5

生成最近30天的日期

WITH RECURSIVE cte (`date`) AS (
    SELECT current_date as `date`
    UNION ALL
    SELECT DATE_ADD(`date`, INTERVAL -1 DAY)
     FROM cte
    WHERE `date` > DATE_ADD(current_date, INTERVAL -29 DAY)
)
SELECT
    *
FROM cte;

执行结果

date
2024-01-23
2024-01-22
2024-01-21
2024-01-20
2024-01-19
……
2023-12-25

字符串转为临时表

逗号分隔的字符串转为临时表

SET @str = '2024-01-23,2024-01-22,2024-01-21,2024-01-20';
SET @total = LENGTH(@str) - LENGTH(REPLACE(@str, ',', '')) + 1;
WITH RECURSIVE cte AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM cte
    WHERE num < @total
)
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', num), ',', -1) AS `date`
 FROM cte

执行结果

date
2024-01-23
2024-01-22
2024-01-21
2024-01-20

递归查询

• 数据准备

CREATE TABLE employees (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(100) NOT NULL,
    manager_id INT NULL,
    INDEX (manager_id),
    FOREIGN KEY (manager_id) REFERENCES employees (id)
);

INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

• 递归查询

WITH RECURSIVE employee_paths (id, name, path) AS (
    SELECT
        id,
        name,
        CAST(id AS CHAR(200))
    FROM
        employees
    WHERE
        manager_id IS NULL
    UNION ALL
    SELECT
        e.id,
        e.name,
        CONCAT(ep.path, ',', e.id)
    FROM
        employee_paths AS ep
    JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT
    *
FROM
    employee_paths
ORDER 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