SQLite CTE递归查询 #

-- CTE（Common Table Expression）公用表表达式
-- 临时命名的结果集，只在当前SQL语句中有效

-- 基本语法
WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

-- 示例
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    uo.order_count
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- CTE 优势：
-- 1. 提高可读性
-- 2. 避免重复子查询
-- 3. 支持递归查询
-- 4. 简化复杂查询

-- 单个CTE
WITH high_value_orders AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 1000
)
SELECT 
    u.name,
    hvo.total_spent
FROM users u
JOIN high_value_orders hvo ON u.id = hvo.user_id;

-- 多个CTE
WITH 
    active_users AS (
        SELECT * FROM users WHERE status = 1
    ),
    user_totals AS (
        SELECT user_id, SUM(total) AS total
        FROM orders
        GROUP BY user_id
    ),
    high_spenders AS (
        SELECT * FROM user_totals WHERE total > 1000
    )
SELECT 
    au.name,
    hs.total
FROM active_users au
JOIN high_spenders hs ON au.id = hs.user_id;

-- CTE可以引用前面的CTE
WITH 
    user_orders AS (
        SELECT user_id, COUNT(*) AS order_count
        FROM orders
        GROUP BY user_id
    ),
    active_users AS (
        SELECT u.*
        FROM users u
        JOIN user_orders uo ON u.id = uo.user_id
        WHERE uo.order_count > 0
    )
SELECT * FROM active_users;

-- 递归CTE语法
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
-- ...
-- 10

-- 生成日期序列
WITH RECURSIVE dates AS (
    SELECT date('2024-01-01') AS date
    
    UNION ALL
    
    SELECT date(date, '+1 day') FROM dates 
    WHERE date < '2024-01-10'
)
SELECT * FROM dates;

-- 输出：
-- date
-- ----------
-- 2024-01-01
-- 2024-01-02
-- ...
-- 2024-01-10

-- 组织结构表
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER
);

INSERT INTO employees VALUES
    (1, 'CEO', NULL),
    (2, 'VP Sales', 1),
    (3, 'VP Engineering', 1),
    (4, 'Sales Manager', 2),
    (5, 'Developer 1', 3),
    (6, 'Developer 2', 3);

-- 查询组织层级
WITH RECURSIVE org_chart AS (
    -- 基础查询：顶级员工
    SELECT 
        id, 
        name, 
        manager_id, 
        0 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属员工
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        oc.level + 1,
        oc.path || ' > ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    printf('%*s%s', level * 2, '', name) AS org_structure,
    level,
    path
FROM org_chart
ORDER BY path;

-- 输出：
-- org_structure | level | path
-- --------------|-------|------------------------
-- CEO           | 0     | CEO
--   VP Sales    | 1     | CEO > VP Sales
--     Sales Manager | 2 | CEO > VP Sales > Sales Manager
--   VP Engineering | 1 | CEO > VP Engineering
--     Developer 1 | 2   | CEO > VP Engineering > Developer 1
--     Developer 2 | 2   | CEO > VP Engineering > Developer 2

-- 分类表
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT,
    parent_id INTEGER
);

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

-- 查询分类树
WITH RECURSIVE category_tree AS (
    SELECT 
        id, 
        name, 
        parent_id, 
        0 AS level,
        name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        c.id, 
        c.name, 
        c.parent_id, 
        ct.level + 1,
        ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    printf('%*s%s', level * 2, '', name) AS tree,
    path
FROM category_tree
ORDER BY path;

-- 查询某个员工的所有上级
WITH RECURSIVE ancestors AS (
    -- 基础查询：当前员工
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 6  -- Developer 2
    
    UNION ALL
    
    -- 递归查询：上级
    SELECT e.id, e.name, e.manager_id, a.level + 1
    FROM employees e
    JOIN ancestors a ON e.id = a.manager_id
)
SELECT * FROM ancestors ORDER BY level DESC;

-- 输出：
-- id | name            | manager_id | level
-- ---|-----------------|------------|-------
-- 1  | CEO             | NULL       | 2
-- 3  | VP Engineering  | 1          | 1
-- 6  | Developer 2     | 3          | 0

-- 图结构表
CREATE TABLE graph (
    from_node TEXT,
    to_node TEXT
);

INSERT INTO graph VALUES
    ('A', 'B'),
    ('A', 'C'),
    ('B', 'D'),
    ('B', 'E'),
    ('C', 'F'),
    ('D', 'G');

-- 查找从A出发的所有路径
WITH RECURSIVE paths AS (
    SELECT 
        from_node AS start,
        to_node AS end,
        from_node || ' -> ' || to_node AS path,
        1 AS length
    FROM graph
    WHERE from_node = 'A'
    
    UNION ALL
    
    SELECT 
        p.start,
        g.to_node,
        p.path || ' -> ' || g.to_node,
        p.length + 1
    FROM paths p
    JOIN graph g ON p.end = g.from_node
    WHERE p.length < 10  -- 防止无限循环
)
SELECT * FROM paths ORDER BY length;

-- 输出：
-- start | end | path          | length
-- ------|-----|---------------|--------
-- A     | B   | A -> B        | 1
-- A     | C   | A -> C        | 1
-- A     | D   | A -> B -> D   | 2
-- A     | E   | A -> B -> E   | 2
-- A     | F   | A -> C -> F   | 2
-- A     | G   | A -> B -> D -> G | 3

-- 查找最短路径
WITH RECURSIVE paths AS (
    SELECT 
        from_node AS start,
        to_node AS end,
        from_node || ' -> ' || to_node AS path,
        1 AS length
    FROM graph
    WHERE from_node = 'A'
    
    UNION ALL
    
    SELECT 
        p.start,
        g.to_node,
        p.path || ' -> ' || g.to_node,
        p.length + 1
    FROM paths p
    JOIN graph g ON p.end = g.from_node
    WHERE p.length < 10
)
SELECT path, length
FROM paths
WHERE end = 'G'
ORDER BY length
LIMIT 1;

-- 输出：
-- path              | length
-- -------------------|--------
-- A -> B -> D -> G   | 3

-- 斐波那契数列
WITH RECURSIVE fibonacci AS (
    SELECT 
        0 AS n,
        0 AS value,
        1 AS next_value
    
    UNION ALL
    
    SELECT 
        n + 1,
        next_value,
        value + next_value
    FROM fibonacci
    WHERE n < 10
)
SELECT n, value FROM fibonacci;

-- 输出：
-- n  | value
-- ---|-------
-- 0  | 0
-- 1  | 1
-- 2  | 1
-- 3  | 2
-- 4  | 3
-- 5  | 5
-- 6  | 8
-- 7  | 13
-- 8  | 21
-- 9  | 34
-- 10 | 55

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

-- 输出：
-- n  | fact
-- ---|-------
-- 1  | 1
-- 2  | 2
-- 3  | 6
-- 4  | 24
-- 5  | 120
-- ...

-- 填充缺失日期
CREATE TABLE daily_sales (
    date DATE,
    amount REAL
);

INSERT INTO daily_sales VALUES
    ('2024-01-01', 100),
    ('2024-01-03', 150),
    ('2024-01-06', 200);

-- 生成日期范围并填充
WITH RECURSIVE date_range AS (
    SELECT 
        (SELECT MIN(date) FROM daily_sales) AS date
    
    UNION ALL
    
    SELECT date(date, '+1 day')
    FROM date_range
    WHERE date < (SELECT MAX(date) FROM daily_sales)
)
SELECT 
    dr.date,
    COALESCE(ds.amount, 0) AS amount
FROM date_range dr
LEFT JOIN daily_sales ds ON dr.date = ds.date;

-- 使用递归计算累计值
WITH RECURSIVE 
    ordered_data AS (
        SELECT 
            id,
            amount,
            ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM sales
    ),
    cumulative AS (
        SELECT 
            id,
            amount,
            rn,
            amount AS running_total
        FROM ordered_data
        WHERE rn = 1
        
        UNION ALL
        
        SELECT 
            od.id,
            od.amount,
            od.rn,
            c.running_total + od.amount
        FROM ordered_data od
        JOIN cumulative c ON od.rn = c.rn + 1
    )
SELECT id, amount, running_total FROM cumulative;

-- 始终设置终止条件
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100  -- 终止条件
)
SELECT * FROM numbers;

-- 递归深度限制
-- SQLite 默认递归深度限制为 1000

-- 设置递归深度限制
PRAGMA recursive_triggers = 1000;

-- 对于大数据集，考虑使用其他方法