SQLite 的 CTE（公用表表达式）如何使用？

SQLite 的 CTE（Common Table Expressions，公用表表达式）提供了更灵活的查询方式：

1. CTE 的概念

   • CTE 是临时的命名结果集，在单个语句的执行范围内存在
   • 使复杂查询更易读、更易维护
   • 可以递归使用，实现层次查询
   • SQLite 3.8.3+ 支持 CTE

2. CTE 语法

   sql
   WITH cte_name AS (
       cte_query
   )
   SELECT * FROM cte_name;

3. 基本 CTE 示例

   sql
   -- 简单 CTE
   WITH high_salary_employees AS (
       SELECT * FROM employees WHERE salary > 50000
   )
   SELECT * FROM high_salary_employees ORDER BY salary DESC;
   
   -- 多个 CTE
   WITH 
       dept_avg AS (
           SELECT department, AVG(salary) as avg_salary
           FROM employees
           GROUP BY department
       ),
       high_avg_depts AS (
           SELECT * FROM dept_avg WHERE avg_salary > 60000
       )
   SELECT * FROM high_avg_depts;

4. 递归 CTE

   sql
   -- 递归 CTE 语法
   WITH RECURSIVE cte_name AS (
       -- 初始查询（锚点成员）
       initial_query
       UNION ALL
       -- 递归查询（递归成员）
       recursive_query
   )
   SELECT * FROM cte_name;
   
   -- 示例：生成数字序列
   WITH RECURSIVE numbers(n) AS (
       SELECT 1
       UNION ALL
       SELECT n + 1 FROM numbers WHERE n < 10
   )
   SELECT * FROM numbers;
   
   -- 示例：层次查询（组织结构）
   WITH RECURSIVE org_chart(id, name, manager_id, level) AS (
       SELECT id, name, manager_id, 0
       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
   )
   SELECT * FROM org_chart ORDER BY level, id;

5. CTE 的优势

   • 提高可读性：将复杂查询分解为逻辑部分
   • 代码重用：在同一查询中多次引用 CTE
   • 性能优化：某些情况下比子查询更高效
   • 递归查询：支持层次结构和图遍历

6. CTE 与子查询的比较

   sql
   -- 使用子查询
   SELECT * FROM (
       SELECT department, AVG(salary) as avg_salary
       FROM employees
       GROUP BY department
   ) WHERE avg_salary > 60000;
   
   -- 使用 CTE（更清晰）
   WITH dept_avg AS (
       SELECT department, AVG(salary) as avg_salary
       FROM employees
       GROUP BY department
   )
   SELECT * FROM dept_avg WHERE avg_salary > 60000;

7. 实际应用场景

   sql
   -- 场景1：计算移动平均
   WITH monthly_sales AS (
       SELECT 
           strftime('%Y-%m', order_date) as month,
           SUM(amount) as total
       FROM orders
       GROUP BY month
   )
   SELECT 
       month,
       total,
       AVG(total) OVER (
           ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) as moving_avg
   FROM monthly_sales;
   
   -- 场景2：查找重复记录
   WITH duplicate_emails AS (
       SELECT email, COUNT(*) as cnt
       FROM users
       GROUP BY email
       HAVING cnt > 1
   )
   SELECT u.* FROM users u
   JOIN duplicate_emails d ON u.email = d.email
   ORDER BY u.email;
   
   -- 场景3：路径查找（图遍历）
   WITH RECURSIVE path(start_node, end_node, path, depth) AS (
       SELECT id, id, CAST(id AS TEXT), 0
       FROM nodes
       WHERE id = 1
       UNION ALL
       SELECT p.start_node, e.to_node, p.path || '->' || e.to_node, p.depth + 1
       FROM path p
       JOIN edges e ON p.end_node = e.from_node
       WHERE p.depth < 5 AND INSTR(p.path, e.to_node) = 0
   )
   SELECT * FROM path WHERE end_node = 10;

8. CTE 的限制

   • CTE 只在定义它的语句中可见
   • 不能在 CTE 中使用聚合函数和窗口函数的混合
   • 递归 CTE 需要明确的终止条件

9. 性能考虑

   • CTE 通常被优化器视为内联视图
   • 递归 CTE 可能消耗大量资源
   • 复杂 CTE 可能需要手动优化
   • 考虑使用临时表处理大数据集

CTE 是 SQLite 编写复杂查询的重要工具，特别是递归查询。