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

Question

一个树 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"

Answer 1

CREATE PROCEDURE dbo.GetAllChildID
@ParentID INT,
@ChildIDStr VARCHAR(500) OUTPUT
AS
SET @ChildIDStr=''
SELECT @ChildIDStr=@ChildIDStr+CAST(ID AS VARCHAR(4))+',' FROM TableName WHERE ParentID=@ParentID
IF @ChildIDStr<>'' 
SET @ChildIDStr=LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)
GO

Answer 2

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE PROCEDURE [dbo].[getSubIds]
@ParentId int,
@retStr VARCHAR(100) OUTPUT
AS
BEGIN
--定义临时变量
DECLARE @id int --初始化返回字符串
SET @retStr = ''
--定义游标
DECLARE cur CURSOR FOR select ID from dbo.Product where ParentID = @ParentId
--打开游标
OPEN cur
--提取游标内的行
FETCH cur INTO @id
--声明用来存储查询结果的游标
IF (@@fetch_status <> 0)
    BEGIN
CLOSE cur
DEALLOCATE cur
RETURN
    END
SET NOCOUNT ON
--循环
WHILE (@@fetch_status = 0)
BEGIN
--如果是第一个ID
IF( @retStr = '' )
BEGIN
SET @retStr = @retStr + CONVERT(VARCHAR(10),@id)
END
ELSE
BEGIN
SET @retStr = @retStr + ',' + CONVERT(VARCHAR(10),@id)
END
--提取游标内的行
FETCH cur INTO @id
END
--关闭游标
CLOSE cur
    DEALLOCATE cur
    RETURN
END

Answer 3

1楼和我的都不对，都没有递归，加上递归就好了：ALTER PROCEDURE [dbo].[GetAllChildID]
@ParentID INT,
@ChildIDStr VARCHAR(500) OUTPUT
AS
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

Answer 4

to:Whislly 
用不著使用遞歸的，性能太差

Answer 5

--创建测试表
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) OUTPUT
AS
SET @ChildIDStr=''
IF @ParentID IS NULL RETURN
DECLARE @OldRecordCnt INT
DECLARE @CurRecordCnt INT
CREATE Table #tmp ([ID] INT PRIMARY KEY NOT NULL)
INSERT INTO #tmp VALUES(@ParentID)
SET @OldRecordCnt=1
WHILE 1=1
BEGIN
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=@CurRecordCnt
END
DELETE FROM #tmp WHERE [ID]=@ParentID
SELECT @ChildIDStr=@ChildIDStr++CAST(ID AS VARCHAR(4))+',' FROM #tmp
IF @ChildIDStr<>'' 
SET @ChildIDStr=LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)
DROP TABLE #tmp
GO
--查询测试
DECLARE @ParentID INT
DECLARE @ChildIDStr VARCHAR(500)
SET @ParentID=2
EXEC dbo.GetAllChildID @ParentID,@ChildIDStr OUTPUT
SELECT @ChildIDStr----------------------------------------------------
--测试结果
4,6,7,8,10

Answer 6

楼上正解，效率应该比用递归高吧。。SQL代码规范。。

Answer 7

To:boblaw 
把临时表改成Table类型的变量,效率应该更高一些:CREATE Table #tmp ([ID] INT PRIMARY KEY NOT NULL)
--把上面这句改成:
DECLARE @tmp table ([ID] INT PRIMARY KEY NOT NULL)
--去掉下面句:
DROP TABLE #tmp这样就不用在每次调用存储过程时都创建临时表了.

Answer 8

to:Whislly  
不错,声明变量要好一些