mysql中的with

WITH customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    order_count,
    total_amount
FROM customer_orders
ORDER BY total_amount DESC;

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, manager_id
    FROM employees
    WHERE id = 1 -- 假设我们要查询ID为1的员工及其下属
    UNION ALL
    SELECT e.id, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id
FROM employee_hierarchy;

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, manager_id
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT e.id, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id
FROM employee_hierarchy
OPTION (MAXRECURSION 100); -- 限制递归深度为100

-- 将CTE转换为临时表
CREATE TEMPORARY TABLE temp_customer_orders AS
WITH customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_orders;

-- 使用临时表进行查询
SELECT * FROM temp_customer_orders
ORDER BY total_amount DESC;