TSql 分层和递归查询
1，需求分析
在TSql中实现层次结构，例如有这样一种数据结构，省，市，县，乡，村，如何使用一张表表示这种数据结构，并且允许是不对称的，例如，上海市是个直辖市，没有省份。
create table dbo.hierarchy ( ID int not null primary key, --type int not null, ParentID int not null, name varchar(100) not null )
type表示类型，可以设置：省，Type是1；市，type是2，以此类推。
ParentID标识的是父级ID，例如信阳市的ParentID是河南省的ID。
2，插入测试数据
测试数据格式说明了归属关系，博主懒，去掉type字段。
insert into dbo.hierarchy values(1,0,‘河南省‘)
,(2,1,‘信阳市‘),(3,2,‘淮滨县‘),(4,3,‘芦集乡‘),(12,3,‘邓湾乡‘),(13,3,‘台头乡‘),(14,3,‘谷堆乡‘) ,(8,2,‘固始县‘),(9,8,‘李店乡‘) ,(10,2,‘息县‘),(11,10,‘关店乡‘) ,(5,1,‘安阳市‘),(6,5,‘滑县‘),(7,6,‘老庙乡‘) ,(15,1,‘南阳市‘),(16,15,‘方城县‘) ,(17,1,‘驻马店市‘),(18,17,‘正阳县‘) select * from dbo.hierarchy order by ParentID
3，递归查询
由于实际的数据可能有很多，所以，要想获取河南省下的所有市，县，乡，村等信息，必须使用递归查询，如何编写递归查询？博主对递归查询不是很熟，但是有简单的认识
- 1，初始条件
- 2，调用自身
- 3，终止条件
4，在TSQL中，CTE能够很好的支持递归查询，递归查询不支持子查询
;with cte as ( select * from dbo.hierarchy where id=1 union all select * from dbo.hierarchy where ParentID in(select id from cte) and id not in(select id from cte) ) select * from cte
Sql Server 报错
Msg 465, Level 16, State 1, Line 11
Recursive references are not allowed in subqueries.
5，将子查询修改为inner join
;with cte(Id,ParentID,Name) as ( select * from dbo.hierarchy where id=1 union all select h.* from dbo.hierarchy h inner join cte c on h.ParentID=c.id --where c.id!=h.ID ) select * from cte order by ParentID
如果要查看向内递归到多少level，可以使用派生列，level=0是省level，level=1是市level，依次类推。
;with cte(Id,ParentID,Name,Level) as ( select ID,ParentID,Name,0 as Level from dbo.hierarchy where id=1 union all select h.ID,h.ParentID,h.Name,c.Level+1 as Level from dbo.hierarchy h inner join cte c on h.ParentID=c.id --where c.id!=h.ID ) select * from cte order by ParentID
6，TSql CTE 递归查询原理（摘自网上）
递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。
递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。限制递归次数上限的方法是使用MAXRECURION。
原文：http://www.cnblogs.com/ljhdo/p/4577065.html