有的时候我们希望在一条sql中查询所有记录对应的所有子节点 ,下面说方法:
(1)创建一个查询所有子节点的函数
CREATE DEFINER=root
@%
function getChildrenStr
(pId varchar(255)) RETURNS VARCHAR(100)
BEGIN
DECLARE sTemp VARCHAR(500);
set sTemp = (
select group_concat(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_organization t order by parent_id, id
) t1,
(select @pids := pId ) t2
) t3 where ischild != 0
);
RETURN sTemp;
END
(2) 直接使用函数
select id , getChildrenStr(id) as cIds from t
所以,关键是先把
select group_concat(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_organization t order by parent_id, id
) t1,
(select @pids := pId ) t2
) t3 where ischild != 0
这么一条sql 先写出来