六、树节点的获取

--向下获取SQLDECLARE @ID INTSET @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

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