SQL CTE 表达式

CTE（Common Table Expressions）是一种临时结果集，可以在查询中引用，提高查询的可读性和可维护性。

基本语法

WITH cte_name AS (
    SELECT column1, column2 FROM table_name
)
SELECT * FROM cte_name;

简单 CTE

-- 计算每个部门的平均工资
WITH department_avg AS (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT 
    e.id,
    e.name,
    e.department,
    e.salary,
    d.avg_salary
FROM employees e
JOIN department_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

递归 CTE

-- 递归查询组织结构
WITH RECURSIVE org_hierarchy 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,
        oh.level + 1 AS level
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT * FROM org_hierarchy ORDER BY level, id;

多个 CTE

-- 多个 CTE 组合使用
WITH 
    monthly_sales AS (
        SELECT 
            DATE_TRUNC('month', order_date) AS month,
            SUM(total) AS total_sales
        FROM orders
        GROUP BY DATE_TRUNC('month', order_date)
    ),
    monthly_growth AS (
        SELECT 
            month,
            total_sales,
            LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales,
            (total_sales - LAG(total_sales) OVER (ORDER BY month)) / LAG(total_sales) OVER (ORDER BY month) AS growth_rate
        FROM monthly_sales
    )
SELECT * FROM monthly_growth WHERE growth_rate > 0.1;

CTE 与子查询对比

-- 使用子查询
SELECT * FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
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;

CTE 的优势

1. 可读性: 将复杂查询分解为多个简单部分
2. 可维护性: 易于修改和扩展
3. 可重用性: 同一个 CTE 可以在查询中多次引用
4. 递归支持: 可以处理树形结构数据

实际应用

计算累计销售额

WITH daily_sales AS (
    SELECT 
        order_date,
        SUM(total) AS daily_total
    FROM orders
    GROUP BY order_date
)
SELECT 
    order_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY order_date) AS cumulative_sales
FROM daily_sales;

查找连续登录用户

WITH user_logins AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM user_activity
),
consecutive_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) AS group_key
    FROM user_logins
)
SELECT 
    user_id,
    COUNT(*) AS consecutive_days
FROM consecutive_groups
GROUP BY user_id, group_key
HAVING COUNT(*) >= 7;

注意事项

1. 临时存在: CTE 只在当前查询中有效
2. 性能: 在某些数据库中，CTE 可能不会被优化器视为优化对象
3. 递归限制: 递归 CTE 需要有终止条件，避免无限循环
4. 命名冲突: CTE 名称不能与表名或其他 CTE 名称冲突