MySQL根据父节点id查询所有子节点

方法一

<!--根据id查询子节点-->
    <select id="sonId" parameterType="java.lang.String" resultType="com.tl.visual.model.SystemRelate">
        SELECT class_id FROM (
              SELECT t1.class_id,
              IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', class_id), 0) AS ischild
              FROM (
                   SELECT class_id,parent_id FROM 表名 t WHERE t.state = '01' ORDER BY parent_id, class_id
                  ) t1,
                  (SELECT @pids := #{要查询的id}) t2
             ) t3 WHERE ischild != 0
    </select>

方法二

SELECT *  FROM (
		SELECT t1.*,
		IF(FIND_IN_SET(pid, @pids) > 0, @pids := CONCAT(@pids, ',', id), '0') AS ischild
		FROM (
		SELECT * FROM test2 AS t WHERE t.state = '01' ORDER BY t.id ASC
		) t1,
		(SELECT @pids := 'b1') t2
		) t3 WHERE ischild != '0'

替换的地方：
test2 替换成你的表名
pid 替换成你的父id
id 替换成你的主键id
state 组织状态
b1 替换成你要查询的主键id

看图

测试表结构和数据

create table `test2` (
	`id` varchar (64),
	`pid` varchar (64),
	`pname` varchar (64),
	`state` varchar (64)
); 
insert into `test2` (`id`, `pid`, `pname`, `state`) values('2','b1','开发部','01');
insert into `test2` (`id`, `pid`, `pname`, `state`) values('3','b1','研发部','01');
insert into `test2` (`id`, `pid`, `pname`, `state`) values('a','2','开发部','01');
insert into `test2` (`id`, `pid`, `pname`, `state`) values('b1','0','阿里巴巴','01');

测试结果