MySQL with 的用法

使用 with 创建临时结果集(Common Table Expression - CTE)。

1. 简单 CTE

WITH sales_summary AS (
    SELECT
        product_id,
        SUM(amount) as total_sales
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY product_id
)
SELECT
    p.product_name,
    s.total_sales
FROM products p
JOIN sales_summary s ON p.id = s.product_id;

2. 多个 CTE

WITH
    regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
    ),
    top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
    )
SELECT
    region,
    product,
    SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

3. 递归 CTE, 类比为递归函数

WITH RECURSIVE factorial AS (
    SELECT 1 AS n, 1 AS fact
    UNION ALL
    SELECT n + 1, (n + 1) * fact
    FROM factorial
    WHERE n < 1
)
SELECT * FROM factorial;

WITH RECURSIVE number_seq (num, squared) AS (
    -- Anchor: 初始值
    SELECT 1, 1

    UNION ALL

    -- Recursive: num < 5 是终止条件
    SELECT num + 1, (num + 1) * (num + 1)
    FROM number_seq
    WHERE num < 2
)
SELECT * FROM number_seq;