SQL递归

1.表结构：

表名  tbName

字段：编号 id  pk;名称  name;父级编号 ParentID ;

2.数据：

001                               一级001                                                null

002                               一级002                                                null

001001                         一级子级01                                          001

001002                         一级子级02                                            001

001003                         一级子级03                                            001

002001                         二级子级01                                            002

002002                         二级子级02                                            002

002003                         二级子级03                                            002

00200101                     二级子级01子级01                                 002001

3.SQL  获取某个节点下的所有子节点

CREATE PROC getAllChil(@pid int)
AS
BEGIN    
    WITH cteTree
        AS (SELECT *
              FROM tbName
              WHERE Id = @pid  --第一个查询作为递归的基点(锚点)
            UNION ALL
            SELECT tbName.*     --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。
              FROM
                   cteTree INNER JOIN tbName ON cteTree.Id =tbName.pid) 

        SELECT *
          FROM cteTree   
END

4.结果：exec getAllChil @pid='002'

002                               一级002                                                null

002001                         二级子级01                                            002

002002                         二级子级02                                            002

002003                         二级子级03                                            002

00200101                     二级子级01子级01                                 002001

题外话：其实我们可以在添加一个字段把所有该节点的父级ID都加上。例如这样：

001                      一级001                                    null                    null

002                      一级002                                    null                     null

001001                一级子级01                               001                    001;

001002                一级子级02                               001                   001;

001003                一级子级03                               001                   001;

002001                二级子级01                               002                   002;

002002                二级子级02                               002                   002;

002003                二级子级03                               002                   002;

00200101            二级子级01子级01                    002001             002;002001;

00200102            二级子级01子级02                    002001              002;002001;

0020010101        二级子级01子级01子级01         00200101          002;002001;00200101;

这样，如果要查询002所有的子级，直接like '002;%'即可。

                   

递归的存储过程

/*----------------------------------------------------------*/
/*    [PC1recursion]                                        */
/*----------------------------------------------------------*/
IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[PC1recursion]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 )
DROP PROCEDURE [PC1recursion]
GO
CREATE PROC [PC1recursion]
(
 @tbname VARCHAR(36) = '', --表明
 @id varchar(50)='',--id名称
 @idValue varchar(36)='',--id值
 @pid varchar(1000) = ''--父级ID名称
)
AS
/*
功能：根据表，查询该表吓所有本级以及所有子级数据
参数：
返回：递归返回所有数据
编写：ljr 2017-07-8
测试:
*/
BEGIN
declare @sql varchar(max)
set @sql='WITH cteTree
AS (SELECT *
 FROM '+@tbname+'
 WHERE '+@id+'='''+@idValue+'''  --第一个查询作为递归的基点(锚点)
UNION ALL
SELECT '+@tbname+'.*     --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。
 FROM
  cteTree INNER JOIN '+@tbname+' ON cteTree.'+@id+' ='+@tbname+'.'+@pid+') 
select * from cteTree'
print @sql   
exec (@sql)
END
GO