SqlServer 递归查询

语法

-- 查询字段列表格式为([查询字段名1],[查询字段名2],...)，也可用*代替
-- 需要注意的是此处的查询字段列表中的字段与下方内容中定点成员与递归成员查询字段列表内容需一致
WITH [临时表] [查询字段]  
AS 
( 
	SELECT [查询字段] FROM [Table] WHERE [查询条件]
	UNION ALL 
	---递归成员	
	SELECT [查询字段] FROM [Table]  
		INNER JOIN [临时表] ON [Join 条件]
) 
SELECT [查询字段]  FROM [临时表] 

Demo

WITH temp 
AS (SELECT a.CategoryId,a.ParentCategoryId
    FROM dbo.TItemCategory a
    WHERE CategoryId = 20008
          AND a.Status = 'A'
    UNION ALL
    SELECT a.CategoryId,a.ParentCategoryId
    FROM dbo.TItemCategory AS a
        INNER JOIN temp
            ON a.ParentCategoryId = temp.CategoryId
               AND a.Status = 'A')
SELECT temp.* FROM temp;