对于树结构的查询,在oracle数据库中有现成的函数直接调用,但是在mysql中这部分没有现成的函数可以直接调用,对于树形结构的递归遍历在实际业务中也是非常常见的。本小节做一个记录
向下递归查询
SELECT
ID.LEVEL,
DATA.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
表名,
( SELECT @ids := '条件id', @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
表名 DATA
WHERE
FIND_IN_SET( DATA.id, ID._ids )
ORDER BY
LEVEL,
id
向上递归
SELECT
GROUP_CONCAT( s.name SEPARATOR "," )
FROM
(
SELECT
T2.id,
T2.NAME
FROM
(
SELECT
@r AS _id,
( SELECT @r := PK_FATHERORG FROM sys_mdm_org WHERE PK_ORG = _id ) AS 2v2,
@l := @l + 1 AS lvl
FROM
( SELECT @r := '0001A41000000010JDYT' ) vars,
sys_mdm_org h
WHERE
@r <> 0
) T1
JOIN sys_mdm_org T2 ON T1._id = T2.pk_org
) s
@用来标识用户变量
评论区