一个树
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 < @length) BEGIN --保存ID开始的索引 SET @startIndex = @i --得到ID结束的索引 WHILE((SUBSTRING(@ChildIDStr,@i,1) <> ',')) SET @i = @i + 1 --得到ID SET @SubId = CONVERT(INT,SUBSTRING(@ChildIDStr,@startIndex,@i-@startIndex)) --初始化字符串 SET @SubStr = '' --递归调用 EXECUTE dbo.GetAllChildID @SubId,@SubStr OUTPUT if @SubStr <> '' 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<>'' 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代码规范。。