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$$

DELIMITER ;

 

显示如下：

 

class_id	class_name
4	|---首页
11	|---的沙发的
12	|---发达
18	|---f
5	|---首页2