mysql 递归排序查询备注：producttype 排序表，producttype。ptype父节点 ，producttype 。id 主键，showTreeNodes (IN rootid INT) 函数，参数为起始类型rootid.//我的博客 http://yijianfengvip.blog.163.comdrop PROCEDURE IF EXISTS showTreeNodes;CREATE PROCEDURE showTreeNodes (IN rootid INT)BEGINDECLARE 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.comCALL showTreeNodes(-1); //我的博客 http://yijianfengvip.blog.163.comSELECT concat(SPACE(B.nLevel*2),'┕',A.name)FROM producttype A,tmpLst BWHERE A.ID=B.IDORDER BY B.sCort;
本文转自yjflinchong 51CTO博客，原文链接：http://blog.51cto.com/yjflinchong/1164972，如需转载请自行联系原作者
