WITH / CTE
公用表表达式（WITH / CTE）
公用表表达式（CTE，Common Table Expression）是在单个 SQL 查询中定义的临时结果集，其作用范围仅限于定义它的查询语句。CTE 的主要优点是提高 SQL 的可读性和可维护性，并支持在同一查询中多次引用同一子查询。
语法结构
WITH
<cte_name1> AS (SELECT ...),
[<cte_name2> AS (SELECT ...)],
...
SELECT ... FROM <cte_name1> [JOIN <cte_name2> ...]
参数说明：
<cte_name>
<cte_name>
：公用表表达式的名称，在后续查询中可直接引用。- CTE 之间可以相互引用，但只能引用在其之前定义的 CTE。
- 一个查询中可以定义多个 CTE，用逗号分隔。
使用示例
示例 1：单个 CTE — 筛选高薪员工
查询薪资高于 8000 的员工，并按薪资降序排列。
WITH high_salary AS (
SELECT id, name, dept, salary
FROM doc_test.employees
WHERE salary > 8000
)
SELECT name, dept, salary
FROM high_salary
ORDER BY salary DESC;
执行结果：
+-------+-------------+----------+
| name | dept | salary |
+-------+-------------+----------+
| Alice | Engineering | 12000.00 |
| Bob | Engineering | 9500.00 |
| Carol | Marketing | 8500.00 |
+-------+-------------+----------+
示例 2：多个 CTE — 部门平均薪资与员工对比
先计算各部门平均薪资，再找出薪资高于所在部门平均值的员工。
WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_salary
FROM doc_test.employees
GROUP BY dept
),
above_avg AS (
SELECT e.name, e.dept, e.salary, d.avg_salary
FROM doc_test.employees e
JOIN dept_avg d ON e.dept = d.dept
WHERE e.salary > d.avg_salary
)
SELECT name, dept, salary, ROUND(avg_salary, 2) AS dept_avg
FROM above_avg
ORDER BY dept, salary DESC;
执行结果：
+-------+-------------+----------+----------+
| name | dept | salary | dept_avg |
+-------+-------------+----------+----------+
| Alice | Engineering | 12000.00| 9500.00 |
| Carol | Marketing | 8500.00 | 7500.00 |
+-------+-------------+----------+----------+
示例 3：CTE 与聚合 — 各部门在职员工统计
统计各部门在职员工人数及平均薪资。
WITH active_emp AS (
SELECT dept, salary
FROM doc_test.employees
WHERE is_active = TRUE
)
SELECT
dept,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS max_salary
FROM active_emp
GROUP BY dept
ORDER BY headcount DESC;
执行结果：
+-------------+-----------+------------+------------+
| dept | headcount | avg_salary | max_salary |
+-------------+-----------+------------+------------+
| Engineering | 2 | 10750.00 | 12000.00 |
| Marketing | 2 | 7500.00 | 8500.00 |
| HR | 1 | 6000.00 | 6000.00 |
+-------------+-----------+------------+------------+
示例 4：CTE 与 JOIN — 订单金额汇总
汇总每个客户的订单总金额，并筛选出总金额超过 500 的客户。
WITH order_summary AS (
SELECT customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM doc_test.orders
GROUP BY customer_id
)
SELECT customer_id, order_count, total_amount
FROM order_summary
WHERE total_amount > 500
ORDER BY total_amount DESC;
注意事项
- CTE 的作用域仅限于紧随其后的单条 SQL 语句，不能跨语句引用。
- 后定义的 CTE 可以引用前面已定义的 CTE，但不能向前引用。
- Lakehouse 不支持递归 CTE（
WITH RECURSIVE
WITH RECURSIVE
）。
- CTE 本身不会物化（不会写入磁盘），每次引用都会重新计算；如需复用大量计算结果，建议使用临时表或动态表。
