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
t2.product_id
FROM
(
SELECT
@ids AS _ids,
(
SELECT
@ids := group_concat( product_id )
FROM
sjy_pro_product_material
WHERE
deleted = 0 and
find_in_set( material_id, @ids )) AS cids
FROM
sjy_pro_product_material,
( SELECT @ids := ‘需要的条件id,可以传多个’) b
WHERE
@ids <> ‘’
) t1,
sjy_pro_product_material t2
WHERE
find_in_set ( t2.material_id, t1._ids )
AND t2.deleted = 0 ORDER BY t2.product_id asc;
mysql 递归 不用 with 自定义查询
于 2024-04-18 17:56:37 首次发布