在 PostgreSQL 里，WITH 关键字主要用于 公共表表达式 (CTE, Common Table Expressions)。它的作用就是先定义一个临时的“结果集”（就像一个临时的命名子查询），后续 SQL 可以直接引用，结构更清晰，也方便复用。

基本语法

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name
WHERE ...;

• cte_name：你起的名字（就像一张临时表）。
• AS ( ... )：内部写普通的 SELECT 语句。
• 后续查询可以直接用 cte_name 代替子查询。

常见用法

1. 简化复杂查询

WITH sales_per_day AS (
    SELECT order_date, SUM(amount) AS total
    FROM orders
    GROUP BY order_date
)
SELECT order_date
FROM sales_per_day
WHERE total > 1000;

避免在 WHERE 或 HAVING 中写重复的聚合子查询。

2. 多个 CTE 串联

WITH daily AS (
    SELECT order_date, SUM(amount) AS total
    FROM orders
    GROUP BY order_date
),
high_sales AS (
    SELECT order_date
    FROM daily
    WHERE total > 1000
)
SELECT COUNT(*)
FROM high_sales;

多个 WITH 用逗号分隔，按顺序定义。

3. 递归查询 (递归 CTE)

PostgreSQL 支持 递归 CTE，用于层级数据（树状结构）查询：

WITH RECURSIVE subordinates AS (
    SELECT id, manager_id, name
    FROM employees
    WHERE id = 1
  UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

这可以查出某个员工的所有下属。

4. 写入操作结合 CTE

CTE 不仅能 SELECT，还能配合 INSERT、UPDATE、DELETE。

WITH updated AS (
    UPDATE products
    SET price = price * 1.1
    WHERE category = 'furniture'
    RETURNING id, price
)
SELECT *
FROM updated;