1. 引言
先贴上建表SQL:
DROP TABLE IF EXISTS `t_org`;
CREATE TABLE `t_org` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
插入数据:
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (1, '首级组织', NULL);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (2, '一级组织', 1);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (3, '二级组织', 1);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (4, '三级组织', 1);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (5, '1-1组织', 2);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (6, '1-2组织', 2);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (7, '2-1组织', 3);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (8, '3-1组织', 4);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (9, '3-2组织', 4);
INSERT INTO t_org(`id`, `name`, `parent_id`) VALUES (10, '3-3组织', 4);
2. 遍历所有子节点
模板代码:
select id from (
select t1.id,
if(find_in_set(父级id字段名, @pids) > 0, @pids := concat(@pids, ',', 主键id字段名), 0) as ischild
from (
select 主键id字段名,父级id字段名 from 表名 t order by 父级id字段名, 主键id字段名
) t1,
(select @pids := 需要查询的主键id) t2
) t3 where ischild != 0
例子1:遍历首级组织(id=1)
的所有子级:
select id from (
select t1.id,
if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
from (
select id,parent_id from t_org t order by parent_id, id
) t1,
(select @pids := 2) t2
) t3 where ischild != 0
结果:
例子二:遍历三级组织(id=4)
的所有子级:
select id from (
select t1.id,
if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
from (
select id,parent_id from t_org t order by parent_id, id
) t1,
(select @pids := 4) t2
) t3 where ischild != 0
结果:
3. 遍历所有父节点
待补充。。。