with as 说明，with as 的用法，with as 递归用法，with as 与临时表的区别

SQL Server 中WITH AS短语的使用

[TOC]

## 1. with as 说明
with as 短语，它用来定义一段子查询语句，定义的这段子查询语句可以被整个SQL所应用，可以提高代码的阅读性，我们有时会再一个sql语句中多次使用相同的sql子语句，这样导致代码重复阅读差，性能也受影响，而使用with as定义的短语，它的执行方式是如果这个短语被执行两次以上，优化器会自动将该短语所获取的数据放入一个temp表里，如果只有一次则不会，特别在子语句较为复杂的情况下，使用with as 的好处尤为明显，例如我们的短语里面可以使用Union ALL 来组合多张表的数据，如果使用原始的子语句办法那么执行上会多次的去组合这个短语结果，这样和with as短语的性能对比就很明显了。

## 2. with as 的用法

下面以一个例子来解释一下with as 的用法，我们准备两张表（Customer和User）

+ 客户表：Customer

![](/UploadFiles/images/20201218/6374391284559233404191825.png)

+ 用户表：User

![](/UploadFiles/images/20201218/6374391291921773432209272.png)

**SQL语句如下：**

```SQL
WITH temp
AS (SELECT Name,
           Sex
    FROM dbo.Customer
    WHERE Age > 50
    UNION ALL
    SELECT [User].Name,
           [User].Sex
    FROM dbo.[User]
    WHERE ISNULL([User].Alias, '') <> '')
SELECT U.*
FROM dbo.[User] AS U
WHERE (
          U.Position = '王'
          AND U.Name IN (
                            SELECT temp.Name FROM temp WHERE CHARINDEX('曹', temp.Name) > 0
                        )
      )
      OR (U.Name IN (
                        SELECT temp.Name FROM temp WHERE temp.Sex = 0
                    )
         )
      OR (U.Name IN (
                        SELECT temp.Name FROM temp GROUP BY temp.Name HAVING COUNT(temp.Name) > 1
                    )
         );
```

**CTE 查询结果**

![](/UploadFiles/images/20201221/6374413658939888751448386.png)

## 3. with as 递归用法

在 SQL Server 中，`WITH AS`（公共表表达式，CTE）可用于递归查询，常用于处理层次结构数据（如组织架构、产品分类等）。递归 CTE 由两部分组成：初始查询（种子）和递归查询，两者通过 `UNION ALL` 连接。

### 基本语法
```sql
WITH cte_name (column_list) AS (
    -- 初始查询（种子）：返回基础结果集
    SELECT ...
    FROM ...
    WHERE ...
    
    UNION ALL
    
    -- 递归查询：引用 CTE 自身，逐步扩展结果集
    SELECT ...
    FROM cte_name
    JOIN ...
    WHERE ...
)
-- 使用 CTE 查询最终结果
SELECT * FROM cte_name;
```

### 示例：员工层级结构查询
假设存在以下 `Employees` 表：
```sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    ManagerID INT NULL FOREIGN KEY REFERENCES Employees(EmployeeID)
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'CEO', NULL),
(2, '部门经理A', 1),
(3, '部门经理B', 1),
(4, '员工A', 2),
(5, '员工B', 2),
(6, '员工C', 3);
```

#### 使用递归 CTE 查询所有员工及其上级层级
```sql
WITH EmployeeHierarchy AS (
    -- 初始查询：获取顶层员工（CEO）
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level,          -- 层级深度（CEO 为 0）
        CAST(EmployeeName AS VARCHAR(MAX)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL  -- CEO 没有上级
    
    UNION ALL
    
    -- 递归查询：获取每个经理的下属，层级深度+1
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1,        -- 层级深度递增
        CAST(eh.Path + ' > ' + e.EmployeeName AS VARCHAR(MAX)) AS Path
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
-- 查询最终结果，按层级排序
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    Level,
    Path
FROM EmployeeHierarchy
ORDER BY Level, EmployeeID;
```

### 结果解释
| EmployeeID | EmployeeName | ManagerID | Level | Path               |
|------------|--------------|-----------|-------|--------------------|
| 1          | CEO          | NULL      | 0     | CEO                |
| 2          | 部门经理A     | 1         | 1     | CEO > 部门经理A     |
| 3          | 部门经理B     | 1         | 1     | CEO > 部门经理B     |
| 4          | 员工A        | 2         | 2     | CEO > 部门经理A > 员工A |
| 5          | 员工B        | 2         | 2     | CEO > 部门经理A > 员工B |
| 6          | 员工C        | 3         | 2     | CEO > 部门经理B > 员工C |

### 关键要点
1. **初始查询**：必须返回基础数据（如顶层节点），且不引用 CTE 自身。
2. **递归查询**：通过 `JOIN` 连接 CTE 自身，逐步扩展结果集（如获取下属）。
3. **终止条件**：递归查询必须包含终止逻辑（如 `WHERE` 子句），避免无限循环。
4. **层级控制**：通常使用 `Level` 字段记录层级深度，便于排序和筛选。

### 其他应用场景
1. **组织结构图**：查询员工及其所有下属。
2. **产品分类**：查询类别及其所有子类别。
3. **路径分析**：计算节点间的路径。

### 注意事项
- 递归 CTE 可能导致性能问题，建议在大数据量时添加合理的过滤条件。
- SQL Server 默认限制递归层级为 100 层，可通过 `OPTION (MAXRECURSION n)` 修改（`n` 为 0~32767，0 表示无限制）。

## 4. with as 与临时表的区别
with as查询的结果不是放在临时表中，而是放在了内存当中，使用with as定义的短语，可以在整个SQL中利用，提高了代码的可读性，减少代码重复量，在性能上也得到了提高。

临时表和永久表类似，存储在磁盘的临时缓冲区，只有数据库连接断开或者dorp掉才会消失。