MySQL SQL查询新模式CTE使用详解

一、什么是CTE？

CTE（Common Table Expressions，公共表表达式）是MySQL 8.0引入的重要特性，它允许在查询中定义临时的命名结果集。

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

二、CTE的优势

1. 可读性更强

-- 传统方式：嵌套子查询
SELECT * FROM (
    SELECT * FROM employees WHERE salary > 5000
) AS high_salary_emp;

-- CTE方式：更清晰
WITH high_salary_emp AS (
    SELECT * FROM employees WHERE salary > 5000
)
SELECT * FROM high_salary_emp;

2. 支持递归查询

-- 查询组织结构树
WITH RECURSIVE org_tree AS (
    -- 初始查询：根节点
    SELECT 
        employee_id, 
        name, 
        manager_id,
        1 AS level
    FROM employees 
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归查询：下级员工
    SELECT 
        e.employee_id, 
        e.name, 
        e.manager_id,
        ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree ORDER BY level, employee_id;

三、CTE的使用模式

模式1：简单CTE（提高可读性）

-- 示例：计算部门平均工资
WITH dept_stats AS (
    SELECT 
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.emp_count,
    ds.avg_salary
FROM dept_stats ds
JOIN departments d ON ds.department_id = d.department_id
WHERE ds.avg_salary > 8000;

模式2：多个CTE（链式使用）

WITH 
sales_summary AS (
    SELECT 
        product_id,
        SUM(quantity) AS total_quantity,
        SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_id
),
product_info AS (
    SELECT 
        p.product_id,
        p.product_name,
        p.category,
        s.total_quantity,
        s.total_amount
    FROM products p
    JOIN sales_summary s ON p.product_id = s.product_id
)
SELECT 
    category,
    SUM(total_quantity) AS cat_quantity,
    SUM(total_amount) AS cat_amount
FROM product_info
GROUP BY category
ORDER BY cat_amount DESC;

模式3：递归CTE（层级数据处理）

示例1：生成数字序列

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

示例2：文件路径拆解

WITH RECURSIVE path_parts AS (
    SELECT 
        'a/b/c/d/e.txt' AS full_path,
        1 AS position,
        SUBSTRING_INDEX('a/b/c/d/e.txt', '/', 1) AS part,
        SUBSTRING('a/b/c/d/e.txt', LENGTH(SUBSTRING_INDEX('a/b/c/d/e.txt', '/', 1)) + 2) AS remainder

    UNION ALL

    SELECT 
        full_path,
        position + 1,
        CASE 
            WHEN LOCATE('/', remainder) > 0 
            THEN SUBSTRING_INDEX(remainder, '/', 1)
            ELSE remainder
        END,
        CASE 
            WHEN LOCATE('/', remainder) > 0 
            THEN SUBSTRING(remainder, LENGTH(SUBSTRING_INDEX(remainder, '/', 1)) + 2)
            ELSE ''
        END
    FROM path_parts
    WHERE remainder != ''
)
SELECT * FROM path_parts;

四、CTE的实用场景

场景1：数据清洗和转换

-- 数据清洗：处理缺失值
WITH cleaned_data AS (
    SELECT 
        customer_id,
        COALESCE(name, 'Unknown') AS customer_name,
        COALESCE(email, 'no-email@example.com') AS customer_email,
        CASE 
            WHEN age < 0 THEN NULL
            WHEN age > 120 THEN NULL
            ELSE age
        END AS valid_age
    FROM raw_customers
)
SELECT * FROM cleaned_data WHERE valid_age IS NOT NULL;

场景2：复杂分析查询

-- 计算员工排名和百分比
WITH employee_rank AS (
    SELECT 
        employee_id,
        name,
        salary,
        department_id,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
        PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) * 100 AS percentile
    FROM employees
)
SELECT 
    e.*,
    d.department_name
FROM employee_rank e
JOIN departments d ON e.department_id = d.department_id
WHERE e.dept_rank <= 3;

场景3：数据分页优化

-- 高效分页查询
WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn
    FROM large_table
    WHERE status = 'active'
)
SELECT * FROM ranked_data
WHERE rn BETWEEN 1001 AND 1100;

五、CTE性能优化技巧

1. 使用MATERIALIZED强制物化

-- MySQL 8.0.16+ 支持
WITH /*+ MATERIALIZED */ expensive_cte AS (
    SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM expensive_cte;

2. 避免无限递归

-- 设置递归深度限制
SET SESSION cte_max_recursion_depth = 1000;

WITH RECURSIVE cte AS (
    -- ...
)
SELECT * FROM cte;

3. 合理使用索引

-- 确保CTE查询能使用索引
WITH indexed_cte AS (
    SELECT * FROM orders 
    WHERE order_date >= '2024-01-01'
    -- 确保order_date有索引
)
SELECT * FROM indexed_cte;

六、CTE vs 临时表 vs 子查询

特性	CTE	临时表	子查询
生命周期	单次查询	会话级别	单次查询
性能	优化器可优化	需要手动优化	可能影响性能
可读性	高	中等	低
递归支持	✓	✗	✗
复用性	查询内复用	会话内复用	单次使用

七、实战案例：电商数据分析

-- 分析用户购买行为
WITH 
user_orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(amount) AS total_spent,
        MIN(order_date) AS first_order_date,
        MAX(order_date) AS last_order_date
    FROM orders
    WHERE order_date >= DATE_SUB(NOW(), INTERVAL 90 DAY)
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        user_id,
        order_count,
        total_spent,
        CASE 
            WHEN order_count >= 10 THEN 'VIP'
            WHEN order_count >= 5 THEN 'Regular'
            WHEN order_count >= 1 THEN 'Casual'
            ELSE 'Inactive'
        END AS user_segment,
        DATEDIFF(NOW(), last_order_date) AS days_since_last_order
    FROM user_orders
)
SELECT 
    user_segment,
    COUNT(*) AS user_count,
    AVG(order_count) AS avg_orders,
    AVG(total_spent) AS avg_spent,
    AVG(days_since_last_order) AS avg_inactive_days
FROM user_segments
GROUP BY user_segment
ORDER BY avg_spent DESC;