MySql根据id递归向下查询所有子级
MySql根据id递归向下查询所有子级
·
通过id递归向下查询所有子级,适用于树表结构查询,亲测好用
如果对你有用,结尾处 三连 鼓励一下,感谢 !
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
DATA.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM TblTeacherEvaluateRuleInfo WHERE FIND_IN_SET( parentId, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
TblTeacherEvaluateRuleInfo,
( SELECT @ids := 1, @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
TblTeacherEvaluateRuleInfo DATA
WHERE
FIND_IN_SET( DATA.id, ID._ids )
ORDER BY
id DESC
结果:
字段注释:
以上版本不适用MySQL8.0版本,可以参考下边这个版本
------------------------------------------------------分割线-----------------------------------------------------------------
SELECT
id
FROM
(
SELECT
t1.id,
t1.parent_id,
IF (
find_in_set(parent_id, @pids) > 0,
@pids := concat(@pids, ',', id),
0
) AS childList
FROM
(
SELECT
t.节点id as id,
t.节点父id AS parent_id
FROM
表名 t
ORDER BY
t.parentCode,
t.code
) t1,
(SELECT @pids := '查询节点标识') t2
) t3
WHERE
childList != 0;
示例SQL和结果
查询节点id:上图示例就会查询出id为8的所有孩子节点,
注: 此SQL不会查询自己,只会查询所有子节点
上线前切记线上数据库版本,血的教训
更多推荐
已为社区贡献1条内容
所有评论(0)