根据父节点查询子节点的迭代和递归？

[言多必失]

表结构如下,数据时模拟的到底有多少层不确定

ID ParentID Name
100 0 测试1
100001 100 测试2
100001001 100001 测试3
100002 100 测试4
100002001 100002 测试5
100002001001 100002001 测试6
101 0 测试7
101001 101 测试8

[img=http://img.my.csdn.net/uploads/201405/25/1401023873_7922.jpg]

如果数据的层次是死的我写了2种，
第一种 每个节点有且只有一个子节点,层次只能3层， 顾不符合要求
第二 解决了可以有多个子节点，但是层次也只有3层，
如果有 100002001001001 100002001 测试9
是查询不出来的，虽然再加一个UNION,可以得到正确的数据

但是要是有5层，6层，7层...所以不可能每一次都去修改查询语句

DECLARE @testId NVARCHAR(50);
SET @testId = '100002';
-- 第一种写法
SELECT  *
FROM    dbo.Test
WHERE   ID IN ( @testid,
                ( SELECT    ID
                  FROM      dbo.Test
                  WHERE     ParentID IN ( @testId )
                ),
                ( SELECT    ID
                  FROM      Test
                  WHERE     ParentID IN ( SELECT    ID
                                          FROM      dbo.Test
                                          WHERE     ParentID = @testId )
                )
           );
--第二种写法           
SELECT  *
FROM    dbo.Test
WHERE   ID IN ( @testId)

UNION
SELECT  *
FROM    dbo.Test
WHERE   ID IN ( SELECT  ID
                FROM    dbo.Test
                WHERE   ParentID IN ( @testId ) )
UNION
SELECT  *
FROM    dbo.Test
WHERE   ID IN ( SELECT  ID
                FROM    Test
                WHERE   ParentID IN ( SELECT    ID
                                      FROM      dbo.Test
                                      WHERE     ParentID = @testId ) )

[--小F--]

CTE递归：
;WITH F AS 
(
SELECT * FROM TB WHERE ID='XX'
UNION ALL
SELECT A.* FROM TB AS A INNER JOIN F AS B ON A.ID=B.ParentID
)
SELECT * FROM F

如果你的SQL版本是2000的话 可以用函数 自己去查一下关于BOM方面的资料吧 很多

[yoan2014]

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/?utm_source=simpletalk&utm_medium=publink&utm_content=sqlhierarchy这篇文章有提到很多种写法，并做了性能的对比，你看看是否对你有帮助

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v6.5.278
 * Time: 2014/5/25 22:08:07
 ************************************************************/
 IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
	DROP TABLE test
GO
CREATE TABLE test(id VARCHAR(20) , parentid VARCHAR(20) , NAME NVARCHAR(5))
GO
INSERT INTO test
SELECT '100' , '0' , N'测试1' union all
SELECT '100001' , '100' , N'测试2' union all
SELECT '10001001' , '10001' , N'测试3' union all
SELECT '100002' , '100' , N'测试4' union all
SELECT '100002001' , '100002' , N'测试5' union all
SELECT '100002001001' , '100002001' , N'测试6' union all
SELECT '101' , '0' , N'测试7' union all
SELECT '101001' , '101' , N'测试8' 
GO
--执行查询
DECLARE @TopLevel      VARCHAR(20) = '0' --指定父节点
        ,
        @NumLevels     INT = 3;   -- 指定阶层
WITH HierarchyTraaversal AS
     
     (
         -- rCTE anchor: retrieve the top level node
         SELECT [Level] = 1,
                parentid,
                id,
                name
         FROM   dbo.test
         WHERE  parentid = @TopLevel
         UNION ALL
         -- rCTE recursion: retrieve the following nodes 
         SELECT [Level] + 1,
                a.parentid,
                a.id,
                a.NAME
         FROM   dbo.test a
                JOIN HierarchyTraaversal b
                     ON  b.id = a.parentid
         WHERE  [Level] < @NumLevels --+ 1
     )
SELECT [Level],
       parentid,
       id,
       NAME
FROM   HierarchyTraaversal
ORDER BY
       [Level],
       parentid,
       id;
/*结果
 Level       parentid             id                   NAME
----------- -------------------- -------------------- -----
1           0                    100                  测试1
1           0                    101                  测试7
2           100                  100001               测试2
2           100                  100002               测试4
2           101                  101001               测试8
3           100002               100002001            测试5

(6 行受影响)


 */

[言多必失]

引用 1 楼 fredrickhu 的回复:

CTE递归：
;WITH F AS 
(
SELECT * FROM TB WHERE ID='XX'
UNION ALL
SELECT A.* FROM TB AS A INNER JOIN F AS B ON A.ID=B.ParentID
)
SELECT * FROM F

如果你的SQL版本是2000的话 可以用函数 自己去查一下关于BOM方面的资料吧 很多

非常感谢你的回答， 你给出CTE递归词 很好， 因为我感觉SQL有递归不知道叫什么，也就是说不知道这个方面，所搜都不知道怎么弄，先在知道的了。 我也得出我想要的结果了

[极品老土豆]

/*
ID         ParentID Name
('100','0','测试1'),
 ('100001','100','测试2'),
 ('100001001','100001','测试3'),
 ('100002','100','测试4'),
 ('100002001','100002','测试5'),
 ('100002001001','100002001','测试6'),
 ('101','0','测试7'),
 ('101001','101','测试8')
 */

 declare @a table ( id varchar(30),parentid varchar(20),name varchar(20))
 insert into @a(id,parentid,name)
            values('100','0','测试1'),
 ('100001','100','测试2'),
 ('100001001','100001','测试3'),
 ('100002','100','测试4'),
 ('100002001','100002','测试5'),
 ('100002001001','100002001','测试6'),
 ('101','0','测试7'),
 ('101001','101','测试8')

 declare @parentid varchar(30)
 declare @id varchar(20)
 set @parentid='0'
 set @id =101
 ;with a as 
 (
   select id,parentid,name,1 as lv
   from @a
    where parentid =@parentid and id =@id 
  union all
  select b.id,b.parentid,b.name,lv+1
  from @a as b inner join a on b.parentid = a.id
 )
 select * from a
 order by lv 
 -- set @parentid='0'   set @id =100
 /*
id                             parentid             name                 lv
------------------------------ -------------------- -------------------- -----------
100                            0                    测试1                  1
100001                         100                  测试2                  2
100002                         100                  测试4                  2
100002001                      100002               测试5                  3
100001001                      100001               测试3                  3
100002001001                   100002001            测试6                  4

(6 行受影响)

 */
  -- set @parentid='0'   set @id =101
 /*
(8 行受影响)
id                             parentid             name                 lv
------------------------------ -------------------- -------------------- -----------
101                            0                    测试7                  1
101001                         101                  测试8                  2

(2 行受影响)
*/