WITH AS短语，也叫做子查询部分（subquery factoring），定义一个sql 片段，改sql 片段会被整个sql语句用到。其中最实用的功能就是数据的递归，递归的原理：递归包括至少两个查询，一个查询作为递归的基点也就是起点，另一个查询作为递归的成员。

一、定义一个公司（company）表 ，总公司下面包含子公司，子公司下面又包含分办事处

CREATE TABLE [dbo].[Company]
(
    [id]          [NVARCHAR](50) NOT NULL, --Id 主键
    [companyName] [NVARCHAR](50) NULL, --公司名称
    [pId]         [NVARCHAR](50) NULL --父节点id
) ON [PRIMARY];

二、 插入数据到公司表

INSERT INTO Company (id, companyName, pid) VALUES ('A', 'A总公司', '/'); --总公司
INSERT INTO Company (id, companyName, pid) VALUES ('B', 'B分公司01', 'A'); --分公司
INSERT INTO Company (id, companyName, pid) VALUES ('C', 'C分公司02', 'A'); --分公司
INSERT INTO Company (id, companyName, pid) VALUES ('B1', 'B办事处', 'B'); --办事处
INSERT INTO Company (id, companyName, pid) VALUES ('B2', 'B办事处', 'B'); --办事处
INSERT INTO Company (id, companyName, pid) VALUES ('B3', 'B办事处', 'B'); --办事处
INSERT INTO Company (id, companyName, pid) VALUES ('C1', 'C办事处', 'C'); --办事处
INSERT INTO Company (id, companyName, pid) VALUES ('C2', 'C办事处', 'C'); --办事处
INSERT INTO Company (id, companyName, pid) VALUES ('C3', 'C办事处', 'C'); --办事处

<!-- 查找所有当前节点的所有子节点 >  查找B分公司和下面的办事处

WITH temp AS (
    SELECT * FROM company WHERE id = 'B'
    UNION ALL
    SELECT c.* FROM company c, temp t WHERE c.pid = t.id
)
SELECT * FROM temp;

<!--查询当前节点的所有父节点 > 查找ID为B3的办事处上层管理机构

WITH t AS (
    SELECT * FROM company WHERE id = 'B3'
    UNION ALL
    SELECT c.* FROM t, company c WHERE t.pid = c.id
)
SELECT * FROM t;

<!--删除当前节点和所有子节点> 删除B分公司和以下的办事处

WITH temp AS (
    SELECT * FROM company WHERE id = 'B'
    UNION ALL
    SELECT c.* FROM company c, temp t WHERE c.pid = t.id
)
DELETE FROM company WHERE id IN (SELECT id FROM temp);

三、注意事项

1、with 前边的sql语句需加分号；

2、使用with变量需紧跟with语句；

3、with变量只能紧跟的使用一次；