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 ID.LEVEL,
       DATA.*
FROM (
         SELECT @ids         AS _ids,
                (
                    SELECT @ids := GROUP_CONCAT(id)
                    FROM org_department_dictionary
                    WHERE FIND_IN_SET(fatherid, @ids)
                )            AS cids,
                @l := @l + 1 AS LEVEL
         FROM org_department_dictionary,
              (SELECT @ids := '1771', @l := 0) b @ids IS NOT NULL
     ) ID,
     org_department_dictionary DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY LEVEL,
         id