--------------所有子集数据包括自己--------------------- 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
本文转自王磊的博客博客园博客,原文链接:http://www.cnblogs.com/vipstone/p/5606780.html,如需转载请自行联系原作者