mysql 递归查找所有子节点

select dept_id from (
              select t1.dept_id,t1.parent_id,
              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 := 要查询的节点id) t2
             ) 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;