mysql 树排序_mysql 递归排序查询+树节点生成

mysql 递归排序查询 备注： producttype  排序表，producttype。ptype父节点  ，producttype 。id 主键，showTreeNodes (IN rootid INT) 函数，参数为起始类型rootid. //我的博客 http://yijianfengvip.blog.163.com drop PROCEDURE IF EXISTS  showTreeNodes; CREATE PROCEDURE showTreeNodes (IN rootid INT) BEGIN DECLARE Level int ; drop TABLE IF EXISTS tmpLst; CREATE TABLE tmpLst (   id int,   nLevel int,   sCort varchar(8000) ); Set Level=0 ; INSERT into tmpLst SELECT id,Level,ID FROM producttype WHERE ptype=rootid; WHILE ROW_COUNT()>0 DO   SET Level=Level+1 ;   INSERT into tmpLst    SELECT A.ID,Level,concat(B.sCort,A.ID) FROM producttype A,tmpLst B     WHERE  A.ptype=B.ID AND B.nLevel=Level-1  ; END WHILE; END; //我的博客 http://yijianfengvip.blog.163.com CALL showTreeNodes(-1); //我的博客 http://yijianfengvip.blog.163.com SELECT concat(SPACE(B.nLevel*2),'┕',A.name) FROM producttype A,tmpLst B WHERE A.ID=B.ID ORDER BY B.sCort;