mysql 5.7版本不支持递归算法,所以记录一下此次sql代码:
SELECT *
FROM lc_departments
where is_deleted = 0
and id in (
(SELECT DATA.id
FROM (SELECT @ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM lc_departments WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL
FROM
lc_departments, ( SELECT @ids := #{pId}, @l := 0 ) b
WHERE
@ids IS NOT NULL) ID,
lc_departments depData
WHERE FIND_IN_SET(depData.id, ID._ids)))