SQL 遞迴

declare @CategoryId int = 1;
 
WITH T AS
(
SELECT [CategoryId],ParentID
, 0 AS Level
FROM [Category]
WHERE [CategoryId] = @CategoryId
UNION ALL
SELECT c.[CategoryId],c.ParentID, Level +1
FROM [Category] c
inner JOIN T t1
ON t1.[CategoryId] in (c.ParentID)
)
SELECT * FROM T;