得到所有子集，或所有父集

=============获取子集=================
CREATE FUNCTION F_Cid(@Cls_ID char(3))
RETURNS @t_Level TABLE(Cls_ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @Cls_ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.Cls_ID,@Level
FROM tblClsInfo a,@t_Level b
WHERE a.ParentID=b.Cls_ID
AND b.Level=@Level-1
END
RETURN
END
GO

===============获取父级==================
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
   SET @Level=@Level+1
   INSERT @t_Level SELECT a.PID,@Level
   FROM tb a,@t_Level b
   WHERE a.ID=b.ID
    AND b.Level=@Level-1
END
RETURN
END
GO

转载于:https://www.cnblogs.com/angells/archive/2009/08/14/1546170.html