SQL Server —— 使用CTE 递归获取当前用户及其所有下级用户

CREATE TABLE #t(
	Id INT NOT NULL PRIMARY KEY IDENTITY,
	Name NVARCHAR(20) NOT NULL,
	ParentId INT NULL DEFAULT(NULL)
)
INSERT INTO #t VALUES('张三',NULL),('李四',1),('王五',2),('赵六',1),('田七',3)
SELECT * FROM #t
GO
WITH cte(Id,Name,ParentId,Level) AS(
	SELECT Id,Name,ParentId,0 Level FROM #t WHERE Id = @parentId 
	UNION ALL
	SELECT #t.*,cte.Level+1 FROM #t  JOIN cte ON cte.Id = #t.ParentId
)
SELECT * FROM cte;
SELECT * FROM cte OPTION(MAXRECURSION 2); --最大递归数为2
GO
DROP TABLE #t