SQL高级语法

Copycopy code to clipboard
1WITH RECURSIVE cte (n) AS (
2    SELECT 1
3    UNION ALL
4    SELECT n + 1
5    FROM cte
6    WHERE n < 5
7)
8SELECT
9    *
10FROM cte;

Copycopy code to clipboard
1WITH RECURSIVE cte (`date`) AS (
2    SELECT current_date as `date`
3    UNION ALL
4    SELECT DATE_ADD(`date`, INTERVAL -1 DAY)
5     FROM cte
6    WHERE `date` > DATE_ADD(current_date, INTERVAL -29 DAY)
7)
8SELECT
9    *
10FROM cte;

Copycopy code to clipboard
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 num
5    UNION ALL
6    SELECT num + 1
7    FROM cte
8    WHERE num < @total
9)
10SELECT
11    SUBSTRING_INDEX(SUBSTRING_INDEX(@str, ',', num), ',', -1) AS `date`
12 FROM cte

Copycopy code to clipboard
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 VALUES
10(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);

Copycopy code to clipboard
1WITH RECURSIVE employee_paths (id, name, path) AS (
2    SELECT
3        id,
4        name,
5        CAST(id AS CHAR(200))
6    FROM
7        employees
8    WHERE
9        manager_id IS NULL
10    UNION ALL
11    SELECT
12        e.id,
13        e.name,
14        CONCAT(ep.path, ',', e.id)
15    FROM
16        employee_paths AS ep
17    JOIN employees AS e
18      ON ep.id = e.manager_id
19)
20SELECT
21    *
22FROM
23    employee_paths
24ORDER BY path;