mysql 递归查找所有子节点

1 select dept_id from (
2               select t1.dept_id,t1.parent_id,
3               if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) as ischild
4               from (
5                    select dept_id,parent_id from sys_dept t where t.del_flag = '0' order by parent_id, dept_id
6                   ) t1,
7                   (select @pids := 要查询的节点id) t2
8              ) t3 where ischild != 0;

解释：

@pids := 要查询的节点id   变量定义。

concat()  拼接字符串

find_in_set(parent_id, @pids)  parent_id 字符串是否在@pids字符串中。
　　select find_in_set('2','1,2');返回2
　　select find_in_set('6','1');返回0

if（express1,express2,express3）条件语句，if语句类似三目运算符，当exprss1成立时，执行express2,否则执行express3;

拆分语句：select dept_id,parent_id from sys_dept t where t.del_flag = '0' order by parent_id, dept_id

select @pids := 要查询的节点id

select * from (
                   select dept_id,parent_id from sys_dept t where t.del_flag = '0' order by parent_id, dept_id
                  ) t1,
                  (select @pids :=4) t2

select t1.dept_id,t1.parent_id,t2.*,
if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) as ischild
from (
     select dept_id,parent_id from sys_dept t where t.del_flag = '0' order by parent_id, dept_id
    ) t1,
    (select @pids := 4) t2

select dept_id from (
              select t1.dept_id,t1.parent_id,t2.*,
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) as ischild
              from (
                   select dept_id,parent_id from sys_dept t where t.del_flag = '0' order by parent_id, dept_id
                  ) t1,
                  (select @pids := 4) t2
             ) t3 where ischild != 0;