在TSQL脚本中,也能实现递归查询,SQL Server提供CTE(Common Table Expression),只需要编写少量的代码,就能实现递归查询,递归查询主要用于层次结构的查询,从叶级(Leaf...(maxrecursion 0);当递归查询达到指定或默认的 MAXRECURSION 数量限制时,SQL Server将结束查询并返回错误,如下:
The statement terminated....3,查询路径,在层次结构中查询子节点到父节点的path
WITH cte AS
(SELECT UserID, ManagerID, name, CAST(name AS NVARCHAR(MAX...16, 15, '方城县'),(17, 1, '驻马店市'),(18, 17, '正阳县');
SELECT * FROM dbo.hierarchy ORDER BY ParentID;
3,实现由父级向子级的查询...4,由子级向父级的递归查询
WITH cte AS
(SELECT ID, ParentID, name FROM dbo.hierarchy WHERE id=4 --芦集乡的ID