子陌老大的这个函数是效率最高的解决方法了,建议你还是不要用游标来做。CREATE FUNCTION dbo.f_getChild(@ID VARCHAR(10)) RETURNS @t TABLE(ID VARCHAR(10),PID VARCHAR(10),LEVEL INT) AS BEGIN DECLARE @i INT SET @i = 1 INSERT INTO @t SELECT ID,PID,@i FROM grp WHERE ID = @ID WHILE @@rowcount <> 0 BEGIN SET @i = @i + 1 INSERT INTO @t SELECT a.ID,a.PID,@i FROM grp a,@t b WHERE a.PID=b.ID AND b.Level=@i-1 END RETURN END
RETURNS @t TABLE(ID VARCHAR(10),PID VARCHAR(10),LEVEL INT)
AS
BEGIN
DECLARE @i INT
SET @i = 1
INSERT INTO @t SELECT ID,PID,@i FROM grp WHERE ID = @ID
WHILE @@rowcount <> 0
BEGIN
SET @i = @i + 1
INSERT INTO @t
SELECT a.ID,a.PID,@i
FROM grp a,@t b
WHERE a.PID=b.ID
AND b.Level=@i-1
END
RETURN
END