在SQL中，`WITH`子句用于创建公共表表达式（Common Table Expression，简称CTE）。CTE是一种临时性、非持久的数据对象，用于简化复杂查询并提高可读性和性能。以下是关于CTE的详细说明：
一、基本定义与作用
定义 CTE通过`WITH`子句定义，语法结构为：
```sql
WITH CTE_Name (column1, column2, ...) AS (
SELECT column1, column2, ...
)
SELECT * FROM CTE_Name;
```
其中，`CTE_Name`是CTE的别名，括号内定义了列名及数据来源（如子查询）。
作用
- 简化复杂查询： 将重复或复杂的子查询封装为临时结果集，避免嵌套过深； - 提高可读性
- 性能优化：部分数据库系统（如SQL Server、PostgreSQL）会优化CTE执行计划。
二、使用场景与示例
基本用法 ```sql
WITH Sales_CTE AS (
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales_data
GROUP BY product_id
)
SELECT * FROM Sales_CTE WHERE total_sales > 1000;
```
该示例中，`Sales_CTE`是一个CTE，用于计算每个产品的总销售额，主查询再基于该CTE进行过滤。
嵌套使用
CTE可嵌套使用，例如：
```sql
WITH Department_CTE AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
),
High_Salary_CTE AS (
SELECT e.employee_id, e.name
FROM employees e
JOIN Department_CTE d ON e.department_id = d.department_id
WHERE d.avg_salary > 100000
)
SELECT * FROM High_Salary_CTE;
```
先通过`Department_CTE`计算部门平均薪资，再通过`High_Salary_CTE`筛选出薪资高于10万元的员工。
三、注意事项
兼容性
- 支持数据库： CTE在SQL Server、PostgreSQL、Oracle、Hive、ODPS等数据库中受支持，但MySQL不支持原生CTE（需通过用户定义函数或临时表实现）； - 功能限制
性能优化 - 索引优化：
确保CTE中使用的列有适当索引，避免全表扫描；
- 资源管理：复杂CTE可能占用较多内存，需结合数据库配置调整资源限制。
替代方案 - 临时表：
对于复杂逻辑，可考虑使用物理临时表，但需手动管理创建和销毁；
- 子查询：简单场景下直接使用子查询，但可读性和维护性较差。
通过合理使用CTE，可以显著提升SQL查询的效率和可维护性，尤其适合处理复杂业务逻辑和多表关联场景。
