六、树节点的获取

--向下获取SQL
DECLARE @ID INT
SET @ID = 1 --外部传入
;WITH DEPTS AS(
    SELECT * FROM Tb
    WHERE ID = @ID
    UNION ALL
    SELECT A.*
    FROM Tb A, DEPTS B
    WHERE A.PID = B.ID
)
SELECT * FROM DEPTS

--向上获取SQL
DECLARE @i int,@id int
SET @id = 1  --外部传入
SET @i = 1
DECLARE @tb TABLE(id INT,pid INT,name VARCHAR(50),Level INT)
INSERT INTO @tb SELECT id,pid,name,0 FROM [table] where id = @id
INSERT INTO @tb SELECT id,pid,name,0,@i FROM [table]
WHERE id = (SELECT pid FROM [table] WHERE id = @Id)
--如果没有新的值插入，循环结束
WHILE @@rowcount<>0
BEGIN
	SET @i = @i + 1;
	INSERT INTO @tb
		SELECT a.id,a.pid,a.name,@i
	FROM [table] a, @tb b
	WHERE a.id = b.pid and b.Level = @i - 1
END
SELECT * FROM @tb