临时表与WITH子句（CTE）

临时表和CTE（Common Table Expression）是模块化SQL查询的重要工具。

📊 支持情况

1. 基本CTE

单个CTE

-- MySQL / Oracle / Hive
WITH cte AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM cte WHERE order_count > 5;

多个CTE

-- 多个CTE共享使用
WITH
cte_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
),
cte_users AS (
    SELECT user_id, name, level
    FROM users
)
SELECT
    u.name,
    u.level,
    o.order_count,
    o.total_amount
FROM cte_users u
JOIN cte_orders o ON u.user_id = o.user_id;

嵌套CTE

-- Oracle / MySQL 8.0+
WITH
cte1 AS (
    SELECT user_id, order_date, amount FROM orders
),
cte2 AS (
    SELECT *, SUM(amount) OVER (PARTITION BY user_id) AS user_total
    FROM cte1
)
SELECT * FROM cte2;

2. 递归CTE

树形结构查询

-- MySQL 8.0+
WITH RECURSIVE org AS (
    SELECT emp_id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.emp_id, e.name, e.manager_id, org.level + 1
    FROM employees e
    INNER JOIN org ON e.manager_id = org.emp_id
)
SELECT * FROM org;

数字序列生成

-- MySQL - 生成1-100
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT * FROM nums;

日期序列生成

-- MySQL - 生成30天日期
WITH RECURSIVE dates AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < DATE_ADD(CURDATE(), INTERVAL 29 DAY)
)
SELECT dt FROM dates;

3. 临时表

MySQL 临时表

-- 创建临时表
CREATE TEMPORARY TABLE temp_high_value_users AS
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;

-- 使用临时表
SELECT * FROM temp_high_value_users t
JOIN users u ON t.user_id = u.user_id;

-- 临时表自动删除（会话结束）

Hive 临时表

-- 创建临时表
CREATE TEMPORARY TABLE temp_sales AS
SELECT region, SUM(amount) AS total_sales
FROM sales
WHERE dt >= '2024-01-01'
GROUP BY region;

-- 使用
SELECT * FROM temp_sales;

MaxCompute 表别名

-- MaxCompute不支持临时表，使用WITH替代
WITH temp_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM sales
    WHERE dt >= '2024-01-01'
    GROUP BY region
)
SELECT * FROM temp_sales;

4. MATERIALIZED CTE

MySQL MATERIALIZED

-- MySQL 8.0+ - CTE物化优化
WITH cte AS MATERIALIZED (
    SELECT * FROM large_table WHERE status = 'active'
)
SELECT * FROM cte JOIN other_table ON cte.id = other_table.cte_id;

ClickHouse MATERIALIZED

-- 使用AS子查询物化
SELECT *
FROM (
    SELECT * FROM large_table
) AS cte
ANY LEFT JOIN other_table ON cte.id = other_table.cte_id;

5. 实战案例

场景1：复杂报表

WITH
-- 订单汇总
orders_summary AS (
    SELECT
        DATE(order_date) AS order_date,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY DATE(order_date)
),
-- 用户统计
user_stats AS (
    SELECT
        DATE(order_date) AS order_date,
        COUNT(DISTINCT user_id) AS buyer_count
    FROM orders
    GROUP BY DATE(order_date)
),
-- 合并结果
combined AS (
    SELECT
        o.order_date,
        o.order_count,
        o.total_amount,
        u.buyer_count,
        ROUND(o.total_amount / u.buyer_count, 2) AS avg_per_buyer
    FROM orders_summary o
    JOIN user_stats u ON o.order_date = u.order_date
)
SELECT * FROM combined ORDER BY order_date;

场景2：数据清洗

WITH cleaned_data AS (
    SELECT
        TRIM(name) AS name,
        LOWER(email) AS email,
        CASE
            WHEN status IN ('A', 'Active') THEN 'active'
            WHEN status IN ('I', 'Inactive') THEN 'inactive'
            ELSE 'unknown'
        END AS status,
        COALESCE(phone, 'N/A') AS phone
    FROM raw_users
),
validated AS (
    SELECT *
    FROM cleaned_data
    WHERE email LIKE '%@%' AND email LIKE '%.%'
)
SELECT * FROM validated;

场景3：多次引用

-- 同一个CTE被多次引用
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS sales
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    m1.month,
    m1.sales AS current_month,
    m2.sales AS prev_month,
    ROUND((m1.sales - m2.sales) / NULLIF(m2.sales, 0) * 100, 2) AS growth_pct
FROM monthly_sales m1
LEFT JOIN monthly_sales m2 ON m1.month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(CONCAT(m1.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m');

6. 子查询vs CTE

子查询嵌套

-- 传统子查询
SELECT *
FROM (
    SELECT
        user_id,
        COUNT(*) AS orders,
        SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) o
WHERE o.orders > 5;

CTE改写

-- CTE更清晰
WITH user_summary AS (
    SELECT user_id, COUNT(*) AS orders, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_summary WHERE orders > 5;

CTE优势

1. 可读性：命名清晰，易于理解
2. 可复用：同一CTE可在多个地方引用
3. 可调试：可单独查询CTE
4. 模块化：复杂查询分解为多个CTE

⚠️ 注意事项

1. CTE引用顺序

-- ✅ 正确：先定义后使用
WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 JOIN cte2;

-- ❌ 错误：不能反向引用
WITH cte2 AS (SELECT * FROM cte1), cte1 AS (...) SELECT * FROM cte2;

2. 递归限制

-- MySQL - 默认递归1000次
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 10000  -- 可能报错
)
SELECT * FROM cte;

-- 调整递归深度
SET SESSION cte_max_recursion_depth = 10000;

3. 性能差异

-- CTE在某些情况下可能被多次计算
-- MySQL 8.0+ 使用 MATERIALIZED 提示优化
WITH cte AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM cte WHERE id IN (SELECT id FROM other_table);

📝 练习题

-- 建表
CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    amount DECIMAL(10,2),
    sale_date DATE,
    region STRING
);

INSERT INTO sales VALUES
(1, 101, 500, '2024-01-14', '华东'),
(2, 102, 300, '2024-01-14', '华北'),
(3, 101, 600, '2024-01-15', '华东'),
(4, 103, 800, '2024-01-15', '华南'),
(5, 102, 450, '2024-01-16', '华东');

要求：

1. 使用CTE计算每个产品的总销售额
2. 使用CTE计算每个区域的日销售额
3. 生成7天日期序列
4. 使用CTE计算环比增长

-- 参考答案（MySQL）
-- 1. 产品销售汇总CTE
WITH product_sales AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT * FROM product_sales;

-- 2. 区域日销售CTE
WITH daily_region_sales AS (
    SELECT region, sale_date, SUM(amount) AS daily_sales
    FROM sales
    GROUP BY region, sale_date
)
SELECT * FROM daily_region_sales ORDER BY sale_date, region;

-- 3. 日期序列
WITH RECURSIVE dates AS (
    SELECT '2024-01-14' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2024-01-20'
)
SELECT dt FROM dates;

-- 4. 环比增长CTE
WITH daily_sales AS (
    SELECT sale_date, SUM(amount) AS total
    FROM sales
    GROUP BY sale_date
)
SELECT
    d1.sale_date,
    d1.total AS curr_sales,
    d2.total AS prev_sales,
    ROUND((d1.total - d2.total) / NULLIF(d2.total, 0) * 100, 2) AS mom_pct
FROM daily_sales d1
LEFT JOIN daily_sales d2 ON d1.sale_date = DATE_ADD(d2.sale_date, INTERVAL 1 DAY);