MariaDB CTE递归查询 #

CTE（Common Table Expression）
├── 公用表表达式
├── 临时结果集
├── 只在当前查询有效
├── 提高可读性
└── 支持递归

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

-- 使用CTE
WITH active_users AS (
    SELECT id, name, email FROM users WHERE status = 1
)
SELECT * FROM active_users WHERE name LIKE 'J%';

-- 等价于子查询
SELECT * FROM (
    SELECT id, name, email FROM users WHERE status = 1
) AS active_users WHERE name LIKE 'J%';

-- 定义多个CTE
WITH 
    active_users AS (
        SELECT id, name FROM users WHERE status = 1
    ),
    high_value_orders AS (
        SELECT user_id, SUM(amount) AS total
        FROM orders
        GROUP BY user_id
        HAVING total > 1000
    )
SELECT 
    au.name,
    hvo.total
FROM active_users au
JOIN high_value_orders hvo ON au.id = hvo.user_id;

-- CTE可以在同一查询中多次引用
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    us1.order_count,
    us2.total_amount
FROM users u
JOIN user_stats us1 ON u.id = us1.user_id
JOIN user_stats us2 ON u.id = us2.user_id
WHERE us1.order_count > 5 AND us2.total_amount > 1000;

WITH RECURSIVE cte_name AS (
    -- 基础查询（锚点）
    SELECT ...
    
    UNION ALL
    
    -- 递归查询
    SELECT ... FROM cte_name WHERE ...
)
SELECT ... FROM cte_name;

-- 生成1到10的序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    
    UNION ALL
    
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

-- 计算阶乘
WITH RECURSIVE factorial AS (
    SELECT 1 AS n, 1 AS fact
    
    UNION ALL
    
    SELECT n + 1, fact * (n + 1) FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

+---+------+
| n | fact |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 3 |    6 |
| 4 |   24 |
| 5 |  120 |
+---+------+

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'CEO', NULL),
    (2, 'VP1', 1),
    (3, 'VP2', 1),
    (4, 'Manager1', 2),
    (5, 'Manager2', 2),
    (6, 'Manager3', 3),
    (7, 'Employee1', 4),
    (8, 'Employee2', 4);

-- 查询组织架构
WITH RECURSIVE org_chart AS (
    -- 基础查询：顶级管理者
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属员工
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        oc.level + 1,
        CONCAT(oc.path, ' > ', e.name)
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY path;

+----+-----------+------------+-------+---------------------------+
| id | name      | manager_id | level | path                      |
+----+-----------+------------+-------+---------------------------+
|  1 | CEO       |       NULL |     1 | CEO                       |
|  2 | VP1       |          1 |     2 | CEO > VP1                 |
|  4 | Manager1  |          2 |     3 | CEO > VP1 > Manager1      |
|  7 | Employee1 |          4 |     4 | CEO > VP1 > Manager1 > Employee1 |
|  8 | Employee2 |          4 |     4 | CEO > VP1 > Manager1 > Employee2 |
|  5 | Manager2  |          2 |     3 | CEO > VP1 > Manager2      |
|  3 | VP2       |          1 |     2 | CEO > VP2                 |
|  6 | Manager3  |          3 |     3 | CEO > VP2 > Manager3      |
+----+-----------+------------+-------+---------------------------+

-- 创建分类表
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

INSERT INTO categories VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Phones', 1),
    (4, 'Laptops', 2),
    (5, 'Desktops', 2),
    (6, 'Smartphones', 3),
    (7, 'Feature Phones', 3);

-- 查询分类树
WITH RECURSIVE category_tree AS (
    SELECT 
        id,
        name,
        parent_id,
        1 AS level,
        CAST(name AS CHAR(500)) AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ct.level + 1,
        CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    CONCAT(REPEAT('  ', level - 1), name) AS tree,
    level,
    path
FROM category_tree
ORDER BY path;

+------------------+-------+---------------------------+
| tree             | level | path                      |
+------------------+-------+---------------------------+
| Electronics      |     1 | Electronics               |
|   Computers      |     2 | Electronics > Computers   |
|     Laptops      |     3 | Electronics > Computers > Laptops |
|     Desktops     |     3 | Electronics > Computers > Desktops |
|   Phones         |     2 | Electronics > Phones      |
|     Smartphones  |     3 | Electronics > Phones > Smartphones |
|     Feature Phones|    3 | Electronics > Phones > Feature Phones |
+------------------+-------+---------------------------+

-- 查找某个员工的所有上级
WITH RECURSIVE managers AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 7  -- Employee1
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers;

+----+-----------+------------+
| id | name      | manager_id |
+----+-----------+------------+
|  7 | Employee1 |          4 |
|  4 | Manager1  |          2 |
|  2 | VP1       |          1 |
|  1 | CEO       |       NULL |
+----+-----------+------------+

-- 查找某个经理的所有下属
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 2  -- VP1
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

+----+-----------+------------+
| id | name      | manager_id |
+----+-----------+------------+
|  2 | VP1       |          1 |
|  4 | Manager1  |          2 |
|  5 | Manager2  |          2 |
|  7 | Employee1 |          4 |
|  8 | Employee2 |          4 |
+----+-----------+------------+

-- 创建图（节点关系）
CREATE TABLE edges (
    from_node INT,
    to_node INT,
    weight INT
);

INSERT INTO edges VALUES
    (1, 2, 10),
    (1, 3, 5),
    (2, 4, 20),
    (3, 4, 15),
    (4, 5, 10);

-- 查找从节点1到节点5的所有路径
WITH RECURSIVE paths AS (
    SELECT 
        from_node,
        to_node,
        weight,
        CAST(from_node AS CHAR(100)) AS path,
        weight AS total_weight
    FROM edges
    WHERE from_node = 1
    
    UNION ALL
    
    SELECT 
        e.from_node,
        e.to_node,
        e.weight,
        CONCAT(p.path, ' -> ', e.to_node),
        p.total_weight + e.weight
    FROM edges e
    JOIN paths p ON e.from_node = p.to_node
)
SELECT path, total_weight FROM paths WHERE to_node = 5;

+---------------------+--------------+
| path                | total_weight |
+---------------------+--------------+
| 1 -> 2 -> 4 -> 5    |           40 |
| 1 -> 3 -> 4 -> 5    |           30 |
+---------------------+--------------+

-- 生成日期序列
WITH RECURSIVE date_series AS (
    SELECT '2024-01-01' AS date
    
    UNION ALL
    
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_series
    WHERE date < '2024-01-10'
)
SELECT * FROM date_series;

+------------+
| date       |
+------------+
| 2024-01-01 |
| 2024-01-02 |
| 2024-01-03 |
| 2024-01-04 |
| 2024-01-05 |
| 2024-01-06 |
| 2024-01-07 |
| 2024-01-08 |
| 2024-01-09 |
| 2024-01-10 |
+------------+

-- 填充缺失日期的销售数据
WITH RECURSIVE date_range AS (
    SELECT '2024-01-01' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2024-01-10'
)
SELECT 
    dr.date,
    COALESCE(SUM(s.amount), 0) AS total
FROM date_range dr
LEFT JOIN sales s ON dr.date = s.sale_date
GROUP BY dr.date
ORDER BY dr.date;

-- 限制递归深度
WITH RECURSIVE org_chart AS (
    SELECT 
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.level < 3  -- 限制深度
)
SELECT * FROM org_chart;

-- 使用访问路径防止循环
WITH RECURSIVE org_chart AS (
    SELECT 
        id,
        name,
        manager_id,
        CAST(id AS CHAR(1000)) AS visited
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        CONCAT(oc.visited, ',', e.id)
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE FIND_IN_SET(e.id, oc.visited) = 0  -- 防止循环
)
SELECT * FROM org_chart;

-- 使用CTE：临时、复杂查询
WITH daily_stats AS (
    SELECT DATE(created_at) AS date, COUNT(*) AS count
    FROM orders
    GROUP BY DATE(created_at)
)
SELECT * FROM daily_stats WHERE count > 10;

-- 使用视图：重复使用
CREATE VIEW daily_order_stats AS
SELECT DATE(created_at) AS date, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at);

SELECT * FROM daily_order_stats WHERE count > 10;

-- 使用有意义的CTE名称
WITH 
    active_users AS (...),
    high_value_orders AS (...),
    user_rankings AS (...)
SELECT ...;

-- 避免使用无意义名称
WITH 
    cte1 AS (...),
    cte2 AS (...),
    cte3 AS (...)
SELECT ...;

-- 1. 使用索引
-- 为递归查询中的连接列创建索引

-- 2. 限制递归深度
WHERE level < 10

-- 3. 使用物化提示（如果支持）
WITH cte_name AS MATERIALIZED (...)

-- 4. 避免在递归部分使用复杂计算

WITH RECURSIVE cte AS (
    基础查询（锚点）
    UNION ALL
    递归查询
)
SELECT ...