总计sql 递归查询

create table #EnterPrise
(
  Department nvarchar(50),--部门名称
  ParentDept nvarchar(50),--上级部门
  DepartManage nvarchar(30)--部门经理
)
insert into #EnterPrise select '技术部','总经办','Tom'
insert into #EnterPrise select '商务部','总经办','Jeffry'
insert into #EnterPrise select '商务一部','商务部','ViVi'
insert into #EnterPrise select '商务二部','商务部','Peter'
insert into #EnterPrise select '程序组','技术部','GiGi'
insert into #EnterPrise select '设计组','技术部','yoyo'
insert into #EnterPrise select '专项组','程序组','Yue'
insert into #EnterPrise select '总经办','','Boss'
--查询部门经理是Tom的下面的部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo
/*
Department           ParentDept                DepartManage      rank
--------------- -------------------- ----------------------- -----------
技术部               总经办                    Tom               0
程序组               技术部                    GiGi              1
设计组               技术部                    yoyo              1
专项组               程序组                    Yue               2
*/
--查询部门经理是GiGi的上级部门名称
;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo
/*
Department               ParentDept          DepartManage    rank
-------------------- ----------------------  -----------  -----------
程序组                   技术部                 GiGi           0
技术部                   总经办                 Tom            1
总经办                                          Boss           2
*/

http://topic.csdn.net/u/20100305/10/a45fcf4e-093c-4def-b4a6-78a476d46f98.html

一个典型的应用场景是：在这个自关联的表中，查询以PKID为2的分类包含所有子分类。也许很多情况下，我们不得不用临时表/表变量/游标等。现在我们有了CTE，就简单多了。

　　WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent)
　　AS
　　(SELECT C_Name, PKID, C_Code,C_Parent FROM CategorySelf WHERE PKID = 2
　　UNION ALL
　　SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent
　　FROM CategorySelf P INNER JOIN
　　SimpleRecursive A ON A.PKID = P.C_Parent
　　)
　　SELECT sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode
　　FROM SimpleRecursive sr inner join CategorySelf c
　　on sr.C_Parent=c.PKID

　　查询结果如下：

　　感觉怎么样?如果我只想查询第二层，而不是默认的无限查询下去，可以在上面的SQL后加一个选项 Option(MAXRECURSION 5)，注意5表示到第5层就不往下找了。如果只想找第二层，但实际结果有三层，此时会出错：

　　Msg 530, Level 16, State 1, Line 1

　　The statement terminated. The maximum recursion 1 has been exhausted before statement completion.

　　此时可以通过where条件来解决，而保证不出错，看如下SQL语句：

　　WITH SimpleRecursive(C_Name, PKID, C_Code,C_Parent,Sublevel)
　　AS
　　(SELECT C_Name, PKID, C_Code,C_Parent,0 FROM CategorySelf WHERE PKID = 2
　　UNION ALL
　　SELECT p.C_Name, p.PKID, p.C_Code,p.C_parent,Sublevel+1
　　FROM CategorySelf P INNER JOIN
　　SimpleRecursive A ON A.PKID = P.C_Parent
　　)
　　SELECT sr.C_Name as C_Name, c.C_Name as C_ParentName,sr.C_Code as C_ParentCode
　　FROM SimpleRecursive sr inner join CategorySelf c
　　on sr.C_Parent=c.PKID
　　where SubLevel<=2

　　查询结果：

　　当然，我们不是说CTE就是万能的。通过好的表设计也可以某种程度上解决特定的问题。下面用常规的SQL实现上面这个需求。

　　注意：上面表中有一个字段很重要，就是C_Code，编码 ,格式如"1/2"，“2/5/8"表示该分类的上级分类是1/2,2/5/8

　　这样，我们查询就简单多，查询以PKID为2的分类包含所有子分类：

SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c.C_Parent=s.PKID) as C_ParentName,C_Code as C_ParentCode
　　from CategorySelf c where C_Code like '2/%'

　　查询以PKID为2的分类包含所有子分类，且级别不大于3。

SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c.C_Parent=s.PKID) as C_ParentName,C_Code as C_ParentCode
　　from CategorySelf c where C_Code like '2/%' and C_Level<=3

　　查询结果同上，略去。这里我们看出，有时候，好的表结构设计相当重要。

　　有人很关心性能问题。目前没有测试过。稍后会附上百万级测试报告。不过，有两点理解邀月忘了补充：

　　一、CTE其实是面向对象的，运行的基础是CLR。一个很好的说明是With查询语句中是区分字段的大小写的。即"C_Code"和"c_Code"是不一样的，后者会报错。这与普通的SQL语句不同。

　　二、 这个应用示例重在简化业务逻辑，即便是性能不佳，但对临时表/表变量/游标等传统处理方式是一种业务层次上的简化或者说是优化。
本篇文章来源于：开发学院 http://edu.codepub.com   原文链接：http://edu.codepub.com/2009/1103/17297.php