mysql 查询树型结构

创建存储过程，生成带有层级的表

delimiter //

drop PROCEDURE IF EXISTS showTreeCdb//

CREATE PROCEDURE showTreeCdb (IN rootid bigINT)
BEGIN
DECLARE Level int ;
drop TABLE IF EXISTS tmp_cdb_tree;
CREATE TABLE tmp_cdb_tree (
id bigINT,
nLevel int,
sCort varchar(8000)
);

Set Level=0 ;
INSERT into tmp_cdb_tree SELECT cdid,Level,cdsx FROM tbl_sys_qxcdb WHERE fcdid=rootid;
WHILE ROW_COUNT()>0 DO
SET Level=Level+1 ;
INSERT into tmp_cdb_tree
SELECT A.cdid,Level,concat(B.sCort,A.cdid) FROM tbl_sys_qxcdb A,tmp_cdb_tree B
WHERE A.fcdid=B.ID AND B.nLevel=Level-1 ;
END WHILE;

END;
//

delimiter ;

CALL showTreeCdb(-1);

联查

SELECT concat(SPACE(B.nLevel*8),'+--',A.cdmc),
a.menu_path,
a.cdid,a.fcdid,a.cdsx,a.cdgn,a.cdtb,
a.sfdhcd,a.menu_category,a.default_menu

FROM tbl_sys_qxcdb A,tmp_cdb_tree B

WHERE A.cdid=B.ID and a.scbj =0

ORDER BY B.sCort;