表结构及数据与oracle递归查询子节点中的一致
数据结构稍有不同
drop table if EXISTS city;
create table city(
id int primary key,
name varchar(50) not null,
parent_id int
);
sql
1.找出所有的节点
select id,name,parent_id from city t order by parent_id, id
2.如果节点的 父id 在 选择的节点id中 将该节点添加到选择的节点集合中(递归)
select t1.id,t1.name,parent_id,
if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
from ( select id,name,parent_id from city t order by parent_id, id ) t1,
(select @pids := 2) t2
3.最后将所有子节点返回
select id,name,ischild from (
select t1.id,t1.name,
if(find_in_set(parent_id, @pids) > 0, @pids:= concat(@pids, ',', id), 0) as ischild
from (
select id,name,parent_id from city t order by parent_id, id
) t1,
(select @pids:= 选择的节点id) t2
) t3
where ischild != 0;
关键方法
find_in_set(str,strList) :查询字段(strlist)中包含(str)的结果
参数:str 要查询的字符串,strlist 字段名 参数以”,”分隔 如 (1,2,6,8)
结果:不包含——0, 包含——返回第一个存在的位置:select FIND_IN_SET('2', '1,2,6,8'); 结果 2
if(express1,express2,express3):当exprss1成立时,执行express2,否则执行express3;
自定义函数实现
获取所有下级节点id
drop function if EXISTS getChildren;
CREATE FUNCTION getChildren (city_id INT) RETURNS VARCHAR (4000)
BEGIN
DECLARE result VARCHAR (4000);
DECLARE child VARCHAR (4000);
SET result = '';
SET child = CAST(city_id AS CHAR);
WHILE child IS NOT NULL DO
SET result = CONCAT(result, ',', child);
SELECT
GROUP_CONCAT(id) INTO child
FROM
city
WHERE
FIND_IN_SET(parent_id, child) > 0;
END
WHILE;
RETURN result;
END
调用
select * from city where FIND_IN_SET(id,getChildren(2));
方法/关键字说明
表达式 into 变量名 将 表达式的值插入到变量中(赋值)
group_concat([distinct]要连接的字段 [order by 排序字段 asc/desc] [separator '分隔符']) : 将group by 产生的同一个分组中的值连接起来,返回一个字符串结果
说明:通过使用 distinct 可以去重;如果希望对结果进行排序,可以使用 order by; separator 是一个字符串,缺省为一个逗号,用于分隔连接的字段
注意:若没有明确有group by 语句时,将所有满足条件的结果作为一个分组