MYSQL根据节点向上和向下查询所有节点

最新推荐文章于 2022-09-23 11:58:12 发布

weixin_30639719

最新推荐文章于 2022-09-23 11:58:12 发布

阅读量435

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/yibinboy/p/10015985.html

版权

WITH cte
AS
(
SELECT * ,UnitID AS level FROM UnitTable WHERE UnitID=2
UNION ALL
SELECT g.*,level+1 FROM UnitTable g INNER JOIN cte
ON g.FatherID=cte.UnitID
)
SELECT * FROM cte

WITH cte
AS
(
SELECT *,0 as level FROM UnitTable WHERE FatherID=0
UNION ALL
SELECT g.*,level+1 FROM UnitTable g INNER JOIN cte
ON g.FatherID=cte.UnitID

)
SELECT * FROM cte

WITH cte
AS
(
SELECT * ,UnitID AS level FROM UnitTable WHERE UnitID=2
UNION ALL
SELECT g.*,g.UnitID AS level FROM UnitTable g INNER JOIN cte ON g.UnitId=cte.FatherID
)
SELECT * FROM cte

转载于:https://www.cnblogs.com/yibinboy/p/10015985.html