mysql 实现递归查询

递归查找父类的所有子节点

SELECT * FROM (

          SELECT t1.*,
          
          IF(FIND_IN_SET(parentid, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS ischild
          
          FROM (
               SELECT * FROM tbl_dept t WHERE t.status = 0 ORDER BY parentid, id
               
              ) t1,
              
              (SELECT @pids := 2) t2
              
         ) t3 WHERE ischild != 0

查询结果不包含自己，若想包含自己，将IF(FIND_IN_SET(parentid, @pids) > 0, @pids := CONCAT(@pids, ‘,’, id), 0) AS ischild修改为IF(FIND_IN_SET(parentid, @pids) > 0 || id=@pids, @pids := CONCAT(@pids, ‘,’, id), 0) AS ischild即可
Hibernate 报错 Space is not allowed after parameter prefix ‘:’
解决方式：
//根据子id递归查询所有父节点
SELECT id,NAME,LEVEL,parentid
FROM (
SELECT
@r AS _id,
(SELECT @r := parentid FROM tbl_dept WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 8, @l := 0) vars,
tbl_dept h
WHERE @r <> 0) T1
JOIN tbl_dept T2
ON T1._id = T2.id
ORDER BY id