因项目需要,需根据某个商品类别path,查询该类别下的所有子类别
表 goods-categories(path,parent_id,id)
该处使用的表为临时创建的表 t1(id,parent_id,code)
最终成品代码:
DELIMITER //
drop function if exists f1;
create function f1(tableId int)
returns VARCHAR(200)
begin
declare p1 varchar(2000);
declare p2 varchar(200);
declare p3 varchar(200);
declare p4 varchar(200);
set p1 ='';
set p3 ='';
set p4 ='';
set p2 = cast(tableId as char);
while p2 is not null do
set p1 = concat(p1,',',p3);
set p4 = p2;
select group_concat(id) into p2 from t1
where find_in_set(parent_id,p2)>0;
select group_concat(code) into p3 from t1
where find_in_set(parent_id,p4)>0;
end while;
<