mysql 实现树状查询

最新推荐文章于 2024-07-06 09:22:27 发布

最新推荐文章于 2024-07-06 09:22:27 发布 · 144 阅读

文章标签：

#MySQL #F#

本文介绍了一个MySQL存储过程，用于递归查询渠道分类信息，并按层级和排序字段展示结果。该存储过程采用临时表进行中间结果的存储，通过不断迭代获取所有子级分类。

DELIMITER $$

DROP PROCEDURE IF EXISTS `sx_finance`.`pro_channel_class`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_channel_class`(in parentid int)
begin
    declare level smallint default 0;
    declare cnt int default 0;
    create temporary table tt(class_id int,class_name varchar(20),PARENT_ID int,level smallint,sort varchar(1000));
    create temporary table tt2(class_id int,class_name varchar(20),PARENT_ID int,level smallint,sort varchar(1000));
    insert into tt select class_id,class_name,PARENT_ID,level,cast(class_id as char) from CHANNEL_CLASS where PARENT_ID=parentid;
    select row_count() into cnt;
    insert into tt2 select * from tt;
    while cnt>0 do
        set level=level+1;
        truncate table tt;
        insert into tt select a.class_id,a.class_name, a.PARENT_ID,level,concat(b.sort,a.class_id) from CHANNEL_CLASS a,tt2 b where a.PARENT_ID=b.class_id and b.level=level-1;
        select row_count() into cnt;
        insert into tt2 select * from tt;
    end while;
select class_id, concat(space(a.level*2),'|---',a.class_name) class_name from tt2 a order by sort;
    drop table tt;
    drop table tt2;
end$$