cte 递归查询

向下查询

 

WITH
q
AS
(
  SELECT  Id, ParentId, Name,Sort
  FROM    dbo.ProductClassifications
  WHERE   Id = '3c02a309-1eba-4eaf-9d58-ad732b8df876'
  UNION ALL
  SELECT      a.Id, a.ParentId, a.Name,a.Sort
  FROM        dbo.ProductClassifications AS a
  INNER JOIN  q ON q.id = a.ParentId
)
SELECT  * FROM q ;

 

向上

  INNER JOIN  q ON q.ParentId = a.id

 

 

 

DECLARE @TempTable TABLE (Id VARCHAR(50), PId VARCHAR(50));
INSERT INTO @TempTable (Id, PId)
SELECT t.Id, t.PId
FROM   (VALUES ('a', 'b'), ('b', 'c'), ('c', 'd'), ('a', 'e'), ('e', 'f'), ('aa', 'bb'), ('aa', 'cc'), ('cc', 'dd')) AS t (
Id, PId
                                                                                                                        );
SELECT * FROM @TempTable;
WITH CTE
  AS
  (
    SELECT Id AS PPId, Id, PId, 1 AS lv
    FROM   @TempTable p
    WHERE  NOT EXISTS (SELECT TOP 1 1 FROM @TempTable AS t WHERE p.Id=t.PId)
    UNION ALL
    SELECT PPId, c.Id, c.PId, p.lv+1 AS lv FROM @TempTable AS c INNER JOIN CTE AS p ON p.PId=c.Id
  )
SELECT * FROM CTE;