在存储过程中怎么实现 : 给出一个ID,求出其下所有子ID,并组成字符串

在存储过程中如何实现 : 给出一个ID,求出其下所有子ID,并组成字符串
一个树

ID ParentID Name
1 0
2 1
3 1
4 2
5 3
6 2
7 2
8 7
9 3
10 6
.......
......

在存储过程中如何实现 : 给出一个ID,求出其下所有子ID,并组成字符串
比如: 现在给一个2 如何取出2下所有子ID,组成 "4,6,7,8,10"

[解决办法]
１楼正解
[解决办法]
1楼和我的都不对，都没有递归，加上递归就好了：

SQL code

ALTER PROCEDURE [dbo].[GetAllChildID]    @ParentID INT,    @ChildIDStr VARCHAR(500) OUTPUTAS    DECLARE @i int    DECLARE @startIndex int    DECLARE @length int    DECLARE @SubId  int    DECLARE @SubStr VARCHAR(500)     SET @ChildIDStr=''    SELECT @ChildIDStr=@ChildIDStr+CAST(ID AS VARCHAR(5))+',' FROM dbo.Product WHERE ParentID=@ParentID    IF (@ChildIDStr = '')        RETURN            SET @i = 1    SET @length = LEN(@ChildIDStr)    -- 循环    WHILE( @i &lt; @length)    BEGIN        --保存ID开始的索引        SET @startIndex = @i        --得到ID结束的索引        WHILE((SUBSTRING(@ChildIDStr,@i,1) &lt;&gt; ','))            SET @i = @i + 1        --得到ID        SET @SubId = CONVERT(INT,SUBSTRING(@ChildIDStr,@startIndex,@i-@startIndex))        --初始化字符串        SET @SubStr = ''        --递归调用        EXECUTE dbo.GetAllChildID @SubId,@SubStr OUTPUT        if @SubStr &lt;&gt; ''            SET @ChildIDStr = @ChildIDStr +@SubStr + ','        --指向下一个字符        SET @i = @i + 1    END    --去掉最后一个','    SET @ChildIDStr = LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)GO
[解决办法]
SQL code--创建测试表CREATE TABLE TableName(    [ID] INT PRIMARY KEY NOT NULL,    [ParentID] INT NOT NULL,    [Name] NVARCHAR(50))--插入测试数据INSERT INTO TableName([ID],[ParentID]) VALUES(1,0)INSERT INTO TableName([ID],[ParentID]) VALUES(2,1)INSERT INTO TableName([ID],[ParentID]) VALUES(3,1)INSERT INTO TableName([ID],[ParentID]) VALUES(4,2)INSERT INTO TableName([ID],[ParentID]) VALUES(5,3)INSERT INTO TableName([ID],[ParentID]) VALUES(6,2)INSERT INTO TableName([ID],[ParentID]) VALUES(7,2)INSERT INTO TableName([ID],[ParentID]) VALUES(8,7)INSERT INTO TableName([ID],[ParentID]) VALUES(9,3)INSERT INTO TableName([ID],[ParentID]) VALUES(10,6)GO--创建存储过程CREATE PROCEDURE dbo.GetAllChildID@ParentID INT,@ChildIDStr VARCHAR(500) OUTPUTASSET @ChildIDStr=''IF @ParentID IS NULL RETURNDECLARE @OldRecordCnt INTDECLARE @CurRecordCnt INTCREATE Table #tmp ([ID] INT PRIMARY KEY NOT NULL)INSERT INTO #tmp VALUES(@ParentID)SET @OldRecordCnt=1WHILE 1=1BEGIN    INSERT INTO #tmp SELECT a.[ID] FROM TableName a INNER JOIN #tmp b ON a.[ParentID]=b.[ID]     WHERE a.[ID] NOT IN (SELECT [ID] FROM #tmp)    SELECT @CurRecordCnt=COUNT(*) FROM #tmp    IF @CurRecordCnt=@OldRecordCnt         BREAK    ELSE        SET @OldRecordCnt=@CurRecordCntENDDELETE FROM #tmp WHERE [ID]=@ParentIDSELECT @ChildIDStr=@ChildIDStr++CAST(ID AS VARCHAR(4))+',' FROM #tmpIF @ChildIDStr&lt;&gt;''     SET @ChildIDStr=LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)DROP TABLE #tmpGO--查询测试DECLARE @ParentID INTDECLARE @ChildIDStr VARCHAR(500)SET @ParentID=2EXEC dbo.GetAllChildID @ParentID,@ChildIDStr OUTPUTSELECT @ChildIDStr------------------------------------------------------测试结果4,6,7,8,10
[解决办法]
楼上正解，效率应该比用递归高吧。。

SQL代码规范。。

在存储过程中怎么实现 : 给出一个ID,求

热点推荐