根据父节点id如何取得其下级的所有节点mysql的sql语句_SQL根据指定节点ID获取所有父级节点和子级节点...

--根据指定节点ID获取所有子节点--WITH TEMP AS

(

SELECT* FROM table_name WHERE Id='4' --表的主键ID

UNION ALL

SELECT T0.* FROM TEMP,table_name T0 WHERE TEMP.Id=T0.ParentId --子级ID==父级ID

)

SELECT*FROM TEMP;--根据指定节点ID获取所有父节点--WITH TEMP AS

(

SELECT* FROM table_name WHERE Id='32' --表的主键ID

UNION ALL

SELECT T0.* FROM TEMP,table_name T0 WHERE TEMP.ParentId=T0.Id --父级ID==子级ID

)

SELECT* FROM TEMP;

--多个WITH AS组合使用场景

WITH TEMP AS

(

SELECT

*

FROM cb_DbSpecialTarget WHERE DbSpecialTargetName LIKE '%砌块含量指标%' AND DbSpecialTargetType=1

UNION ALL

SELECT

T0.*

FROM TEMP,cb_DbSpecialTarget T0 WHERE TEMP.ParentGUID=T0.DbSpecialTargetGUID

)

,

TEMP2 AS

(

SELECT *

FROM cb_DbSpecialTarget WHERE DbSpecialTargetName LIKE '%砌块含量指标%' AND DbSpecialTargetType=1

UNION ALL

SELECT

T0.*

FROM TEMP2,cb_DbSpecialTarget T0 WHERE TEMP2.DbSpecialTargetGUID=T0.ParentGUID

)

SELECT * FROM TEMP

UNION

SELECT * FROM TEMP2 ORDER BY HierarchySort