38 公用表表达式（WITH）

-- A single CTE
WITH spend AS (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
)
SELECT c.name, COALESCE(s.total_spent, 0) AS spent
FROM customers_example c
LEFT JOIN spend s ON s.customer_id = c.id
ORDER BY c.id;

-- Multiple CTEs, each named and easy to read
WITH per_customer AS (
  SELECT customer_id, sum(total) AS total_spent, count(*) AS order_count
  FROM orders_example
  GROUP BY customer_id
),
big_spenders AS (
  SELECT * FROM per_customer WHERE total_spent > 30
)
SELECT c.name, b.total_spent, b.order_count
FROM big_spenders b
JOIN customers_example c ON c.id = b.customer_id
ORDER BY b.total_spent DESC;

-- Data-modifying CTE: archive then return what was archived
DROP TABLE IF EXISTS orders_archive;
CREATE TABLE orders_archive (LIKE orders_example INCLUDING ALL);

WITH archived AS (
  DELETE FROM orders_example
  WHERE total < 20
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived
RETURNING id;

-- RECURSIVE CTE: walk an employee hierarchy
WITH RECURSIVE reports AS (
  -- anchor: top-level employees (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE manager_id IS NULL
  UNION ALL
  -- recursive step: rows whose manager appears in the previous level
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees_example e
  JOIN reports r ON e.manager_id = r.id
)
SELECT depth, name FROM reports ORDER BY depth, id;