遞歸處理

--測試資料

CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))

INSERT tb SELECT '001',NULL ,'山東省'

UNION ALL SELECT '002','001','煙台市'

UNION ALL SELECT '004','002','招遠市'

UNION ALL SELECT '003','001','青島市'

UNION ALL SELECT '005',NULL ,'四會市'

UNION ALL SELECT '006','005','清遠市'

UNION ALL SELECT '007','006','小分市'

GO

--查詢指定節點及其所有子節點的函數

CREATE FUNCTION f_Cid(@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 @[email protected]+1

        INSERT @t_Level SELECT a.ID,@Level

        FROM tb a,@t_Level b

        WHERE a.PID=b.ID

            AND [email protected]

    END

    RETURN

END

GO

--調用函數查詢002及其所有子節點

SELECT a.*

FROM tb a,f_Cid(null) b

WHERE a.ID=b.ID