公用表表达式(Common Table Expression)是SQL Server2005版本的引入的一个特性。用于临时存储某些结果集。
WITH AS-做子查询部分(subquery factoring)。
CTE定义：
|
1 2 3 4 5 |
WITH expression_name [(column_name [,...n] )] AS ( cte_query_definition ) |
Demo：
|
1 2 3 4 5 |
WITH B AS ( SELECT * FROM tb1 WHERE id > 10 ) SELECT * FROM B |
CTE 的两个递归的实例：
|
1 2 3 4 5 6 7 |
-- 表结构 TABLE Dept ( id int PRIMARY KEY, parent_id int, name nvarchar(20) ) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 插入表数据 0, 0, 'xxx工厂' 1, 0, '财务部' 2, 0, '人事部' 3, 0, '制造部' 4, 0, '业务部' 5, 4, '销售部' 6, 4, 'MIS' 7, 6, '网络部' 8, 6, '开发部' 9, 8, 'MES' 10, 8,'ERP' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 实例一，查询指定部门下面的所有部门 WITH DEPTS AS( -- 制定部门的数据 SELECT * FROM Dept WHERE name = 'MIS' UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id ) SELECT * FROM DEPTS |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- 实例二, 查询指定部门下面的所有部门信息, 并进行汇总 DECLARE @Dept_name nvarchar(20) SET @Dept_name = N'MIS' WITH DEPTS AS( -- 查询指定部门及其下的所有子部门 -- 指定部门的数据 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id ), DEPTCHILD AS( -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门 SELECT Dept_id = P.id, C.id, C.parent_id FROM DEPTS P, Dept C WHERE P.id = C.parent_id UNION ALL SELECT P.Dept_id, C.id, C.parent_id FROM DEPTCHILD P, Dept C WHERE P.id = C.parent_id ), DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数 SELECT Dept_id, Cnt = COUNT(*) FROM DEPTCHILD GROUP BY Dept_id ) SELECT -- JOIN第1,3个CTE,得到最终的查询结果 D.*, ChildDeptCount = ISNULL(DS.Cnt, 0) FROM DEPTS D LEFT JOIN DEPTCHILDCNT DS ON D.id = DS.Dept_id |
