sql無限遞歸查詢

--------------所有子集資料包括自己---------------------
CREATE PROCEDURE ALLSON
@ID INT
AS
BEGIN
    WITH CTE AS
    (
        SELECT ID,PID,NAME,0 AS LVL FROM TEST1
        WHERE ID = @ID
        UNION ALL
        SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
        ON C.ID = D.PID
    )
    SELECT * FROM CTE
END


------------------所有父級資料-------------------
CREATE PROCEDURE ALLFATHER
@ID INT
AS
BEGIN
    WITH CTE AS
    (
        SELECT ID,PID,NAME,0 AS LVL FROM TEST1
        WHERE ID = @ID
        UNION ALL
        SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
        ON C.PID = D.ID
    )
    SELECT * FROM CTE
END


--FATHER
EXEC ALLFATHER 6      

如果本文對你有所幫助，請打賞——1元就足夠感動我：）

聯系郵箱：[email protected]

我的GitHub：

https://github.com/vipstone

關注公衆号：

作者：

王磊

出處：

http://vipstone.cnblogs.com/

本文版權歸作者和部落格園共有，歡迎轉載，請标明出處。