父子id mysql,MYSQL 递归父子

捕获.PNG

子查父 (根据子节点查询所有父节点)

SELECT

T2.*

FROM

(

SELECT

@r AS _id,

(

SELECT

@r := parent_id

FROM

t_sys_organization

WHERE

id = _id

) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := '4', @l := 0) vars,

t_sys_organization h

WHERE

@r <> 0

) T1

JOIN t_sys_organization T2 ON T1._id = T2.id

ORDER BY

T1.lvl DESC

父查子 (根据节点查询所有字点)

SELECT

ID.level,

DATA.*

FROM

(SELECT

@ids AS _ids,

(SELECT

@ids := GROUP_CONCAT(id)

FROM

t_sys_organization

WHERE FIND_IN_SET(parent_id, @ids)) AS cids,

@l := @l + 1 AS LEVEL

FROM

t_sys_organization,

(SELECT

@ids := '1',

@l := 0) b

WHERE @ids IS NOT NULL) id,

t_sys_organization DATA

WHERE FIND_IN_SET(DATA.id, ID._ids)

ORDER BY LEVEL,

id ;

查询结果

捕获.PNG

父查子(方法二)

SELECT

O2.*

FROM

(SELECT

@r AS _id,

(SELECT

@r := id

FROM

t_sys_organization

WHERE parent_id = _id) AS id,

@l := @l + 1 AS lvl

FROM

(SELECT

@r := '1',

@l := 0) vars,

t_sys_organization h) O1

JOIN t_sys_organization O2

ON O1._id = O2.parent_id

WHERE O2.enabled = 1

ORDER BY O1.lvl