捕獲.PNG
子查父 （根據(jù)子節(jié)點查詢所有父節(jié)點）
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
父查子 （根據(jù)節(jié)點查詢所有字點）
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
