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 DOSET Level=Level+1 ;INSERT into tmpLstSELECT A.ID,Level,concat(B.sCort,A.ID) FROM producttype A,tmpLst BWHERE 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.ID
ORDER BY B.sCort;
本文转自yjflinchong 51CTO博客,原文链接:http://blog.51cto.com/yjflinchong/1164972,如需转载请自行联系原作者