mysql 单标递归_Mysql中实现递归查询

DELIMITER //

DROP PROCEDURE IF EXISTS Pro_GetColumnOrg//

CREATE PROCEDURE Pro_GetColumnOrg(IN idd VARCHAR(36))

BEGIN

DECLARE lev INT;

SET lev=1;

DROP TABLE IF EXISTS tmp1;

CREATE TABLE tmp1(ID VARCHAR(36),OrgName VARCHAR(50),ParentID VARCHAR(36) ,levv INT);

INSERT tmp1 SELECT s.id,s.name,s.parent_id,1 FROM `主体表` s WHERE parent_id=idd;

WHILE ROW_COUNT()>0

DO

SET lev=lev+1;

INSERT tmp1 SELECT t.id,t.`name`,t.`parent_id`,lev FROM `主体表` t JOIN tmp1 a ON t.parent_id=a.id AND levv=lev-1;

END WHILE ;

INSERT tmp1 SELECT id,NAME,parent_id,1 FROM `sys_column` WHERE ID=idd;

SELECT * FROM tmp1 ORDER BY levv,ParentID;

END;

//

DELIMITER ;