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

mysql 递归排序查询

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