mysql树状查询_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

分享到：

2008-10-10 11:07

浏览 2185

评论