SQL Server 2005参考：CTE 公用表表达式
SQL Server 2005参考：CTE 公用表表达式
当一个查询定义需要被多次调用时,一般可以采用临时表、视图、派生表或者是子查询缓存结果集（或是查询定义），但是，如果这个查询定义只为当前的处理服务，则上面的集中方式都不太合适：
A. 临时表会有额外的I/O开销；
B. 视图是永久性的，不太适合用于临时性定义的处理；
C. 派生表或子查询会增加编写SQL语句的复杂性，也就降低的可读性。
（当然，可读性也是相对的，这里不多谈。）
SQL Server 2005 中新增了公用表表达式（CTE）来解决这样的问题，它是在当前的select、
insert、update、delete或是create view语句执行范围内定义的临时结果集。CTE与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE可自引用，还可在同一查询中引用多次。
这样，可以提高复杂T-SQL语句的可读性和可维护性，查询可以分为单独快、简单块、逻辑生成块，之后这些简单快可以生成更复杂的CTE，知道生成最终结果集。
使用范围
CTE可以在函数、存储过程、触发器或是视图中定义和使用CTE。
同时从使用角度可以分为简单CTE和递归CTE：
（1） 简单CTE，你可以理解为一个简单视图来使用；
（2） 递归CTE，就是CTE可以引用自身，来创建递归的CTE，实现递归查询（早期为实现递归查询需要使用临时表、游标等来实现）。
具体使用参加后面的脚本示例。
语法：
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined(定位定成员).
UNION ALL
CTE_query_definition –- Recursive member is defined referencing
cte_name(递归成员).
)
我们这里将其过程简述如下：
（1） 将CTE表达式拆分为定位点成员和递归成员
（2） 运行定位点成员，创建第一个调用或基准结果（R1），递归的级数为i
（3） 运行递归成员，将Ri作为输入，将Ri+1作为输出，i为递归级数，每将运行递归成员后,i加1.
（4） 重复步骤3，直到返回空集。
（5） 返回结果集。这是对R1到Ri+1进行union all的结果。
使用CTE还有一些注意事项，可以参考Sql server联机丛书的”WITH common_table_expression” 部分内容，同时还可以获得更多的示例。
示例
首先我们创建一个表Table, 只为示例使用，虚构内容
CREATE TABLE dept
(
id INT PRIMARY KEY, -- 部门编号
parent_id INT, -- 所属部门的编号
NAME VARCHAR(20) -- 部门名称
)
INSERT INTO dept
SELECT 0,0,'全部' UNION ALL
SELECT 1,0,'财务部' UNION ALL
SELECT 2,0,'行政部' UNION ALL
SELECT 3,0,'业务部' UNION ALL
SELECT 4,3,'销售部' UNION ALL
SELECT 5,3,'销售部' UNION ALL
SELECT 6,3,'销售部' UNION ALL
SELECT 7,0,'技术部' UNION ALL
SELECT 8,7,'技术部' UNION ALL
SELECT 9,7,'技术部' UNION ALL
SELECT 10,7,'技术部' UNION ALL
SELECT 11,8,'内部研发' UNION ALL
SELECT 12,8,'外联部' UNION ALL
SELECT 13,8,'事业部' UNION ALL
SELECT 14,9,'内部测试' UNION ALL
SELECT 15,9,'外联部' UNION ALL
SELECT 16,9,'知识产权' UNION ALL
SELECT 17,16,'自裁办'
（1）简单CTE
从dept表中获取部门编号为7的直接子部门的信息：
WITH W_1
AS
(
SELECT * FROM dept WHERE parent_id=7
)
SELECT * FROM w_1
结果：
id parent_id NAME
----------- ----------- --------------------
8 7 技术部1
9 7 技术部2
10 7 技术部3
(3 row(s) affected)
（2）覆盖基表的CTE
在本例中定义了两个表t1和t2，然后定义一个名为t2的CTE，该CTE查询t1的内容，随后在CTE定义的有效范围内查询t2,然后在CTE的有效范围外查询t2,通过两个结果对比，一方面说明CTE定义的名称与基表名称冲突时，对该名称的引用实际引用CTE的内容，而非基表的内容；另一方面又说面了，需要在CTE定义后引用它，否则引用是 无效的（语法本身已经限制了）。
--table1
CREATE TABLE t1(id INT);
INSERT INTO t1
SELECT 1 UNION ALL SELECT 2;
--table2
CREATE TABLE t2(id INT);
INSERT INTO t2
SELECT 3 UNION ALL SELECT 4;
SELECT * FROM t1;
WITH t2
as
(
SELECT * FROM t1
)
SELECT * FROM t2;
SELECT * FROM t2;
DROP TABLE t1,t2;
结果：
(2 row(s) affected)
(2 row(s) affected)
id
----------- 来自Table t1
1
2
(2 row(s) affected)
id
----------- 来自CTE t2
1
2
(2 row(s) affected)
id
----------- 来自Table t2
3
4
(2 row(s) affected)
(3) 递归CTE
下面演示通过一个指定的部门编号，查询部门及其下面的所有子部门，使用dept表。
思路：
定义如下CTE dep，在CTE中，首先通过查询基表dept查询出指定的部门（即为 定点成员）；然后通过对这个查询结果的引用（即引用CTE自身），与基表dept做join（递归成员），查询出指定部门的下级部门；由于递归成员会反复执行，直到查询的结果集为空。
DECLARE @sID INT;
SET @sID=7;
WITH dep as
(
--定位点成员
SELECT * FROM dept WHERE id=@sID
UNION ALL
-- 递归成员，通过引用CTE自身与dept基表JOIN实现递归
SELECT dt.* FROM dept dt JOIN dep d ON dt.parent_id=d.id
)
SELECT * FROM dep ORDER BY id
结果：
id parent_id NAME
----------- ----------- --------------------
7 0 技术部
8 7 技术部
9 7 技术部
10 7 技术部
11 8 内部研发
12 8 外联部
13 8 事业部
14 9 内部测试
15 9 外联部
16 9 知识产权
17 16 自裁办
(11 row(s) affected)
（6） 综合应用的CTE
该示例演示的基本需求与示例3一样，由指定的部门编号，查询其及以所包含的所有子部门，在这个结果基础上查询出每个记录对应的部门及其下包含的子部门数（包含其下所有层级的部门）。
首先你需要理解清楚上面的需求。
我们这里定义3个CTE，第一个（同上）查询出指定的部门及其所包含的所有各层级子部门；第2个CTE引用第一个CTE的内容，同样通过递归查询每个子部门（这里的部门由第一个CTE确定）；第3个CTE，仅仅为了做一个汇总，；最后JOIN 1和3这两个CTE得到最后的结果。
DECLARE @sID INT;
SET @sID=7;
WITH d_1 as
(
-- 定位点成员
SELECT * FROM dept WHERE id=@sID
UNION ALL
-- 递归成员，通过引用CTE自身与dept基表JOIN实现递归
SELECT dt.* FROM dept dt JOIN d_1 d ON dt.parent_id=d.id
)
--SELECT * FROM dep ORDER BY id
,
d_2
AS
(
SELECT d_id=dp.id,dt.id,dt.parent_id FROM dept dt JOIN d_1 dp ON dt.parent_id=dp.id
UNION ALL
SELECT dpd.d_id,dd.id,dd.parent_id FROM dept dd JOIN d_2 dpd ON dd.parent_id=dpd.id
)
--SELECT * FROM depchild ORDER BY d_id
,
d_3
AS
(
SELECT d_id,Cnt = COUNT(*) FROM d_2 GROUP BY d_id
)
SELECT d.id,d.[NAME],ChildCount=ISNULL(Cnt,0) FROM d_1 d LEFT JOIN d_3 dc
ON d.id=dc.d_id
结果：
id NAME ChildCount
----------- -------------------- -----------
7 技术部 10
8 技术部 3
9 技术部 4
10 技术部 0
14 内部测试 0
15 外联部 0
16 知识产权 1
17 自裁办 0
11 内部研发 0
12 外联部 0
13 事业部 0
(11 row(s) affected)
