sql无限递归查询

矫情吗；* 2023-10-17 20:54 246阅读 0赞 
  1. --------------所有子集数据包括自己---------------------
  2. CREATE PROCEDURE ALLSON
  3. @ID INT
  4. AS
  5. BEGIN
  6.     WITH CTE AS
  7.     (
  8.         SELECT ID,PID,NAME,0 AS LVL FROM TEST1
  9.         WHERE ID = @ID
  10.         UNION ALL
  11.         SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
  12.         ON C.ID = D.PID
  13.     )
  14.     SELECT * FROM CTE
  15. END
  18. ------------------所有父级数据-------------------
  19. CREATE PROCEDURE ALLFATHER
  20. @ID INT
  21. AS
  22. BEGIN
  23.     WITH CTE AS
  24.     (
  25.         SELECT ID,PID,NAME,0 AS LVL FROM TEST1
  26.         WHERE ID = @ID
  27.         UNION ALL
  28.         SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
  29.         ON C.PID = D.ID
  30.     )
  31.     SELECT * FROM CTE
  32. END
  35. --FATHER
  36. EXEC ALLFATHER 6

发表评论

表情：
评论列表 （有 0 条评论，246人围观）

还没有评论，来说两句吧...

相关阅读