mysql 父id查子节点_MySQL根据父节点id查询所有子节点

方法一

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 * 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');

测试结果