mysql數據庫實現類似oracle connect by prior的查詢 --注下面的語句用到了表 DROP TABLE IF EXISTS `a_sys_org`; CREATE TABLE `a_sys_org` ( `org_id` varchar(32) NOT NULL COMMENT '機構號', `org_name` varchar(256) NOT NULL COMMENT '機構名稱', `parent_org_id` varchar(32) NOT NULL COMMENT '父機構號', PRIMARY KEY (`org_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統機構表'; -- 方法1，函數實現 -- 注意 SELECT getChildOrg('00010'); -- 最大支持字符串連接102400 DROP FUNCTION IF EXISTS getChildidList; CREATE FUNCTION getChildidList(rootId VARCHAR(32)) RETURNS text BEGIN DECLARE sTemp text; DECLARE sTempChd text; SET@@group_concat_max_len = 102400; SET sTemp = '$'; SET sTempChd = rootId; WHILE sTempChd IS NOT NULL DO SET sTemp = concat(sTemp, ',', sTempChd); SELECT group_concat(org_id) INTO sTempChd FROM a_sys_org WHERE FIND_IN_SET(parent_org_id, sTempChd) > 0; END WHILE; SET@@group_concat_max_len = 1024; RETURN SUBSTRING(sTemp,3); END -- 方法2，存儲過程+中間表 -- 存儲過程實現mysql遞歸查詢，類似oracle start with connect by prior -- 注意 call getChildOrg('00010'); -- 遞歸有深度限制，最大是255層 DROP PROCEDURE IF EXISTS getChildOrg; CREATE PROCEDURE getChildOrg (IN rootid VARCHAR(32)) BEGIN DECLARE _level_var INT; DROP TABLE IF EXISTS temp_child_list; CREATE TABLE temp_child_list ( _id VARCHAR(32), _level INT ); SET _level_var = 0; INSERT INTO temp_child_list (_id, _level) VALUE(rootid,_level_var); SET _level_var = _level_var + 1; INSERT INTO temp_child_list (_id, _level) SELECT org_id, _level_var FROM a_sys_org WHERE parent_org_id = rootid; WHILE ROW_COUNT() > 0 DO SET _level_var = _level_var + 1; INSERT INTO temp_child_list SELECT a.org_id, _level_var FROM a_sys_org a, temp_child_list b WHERE a.parent_org_id = b._id AND b._level = _level_var - 1; END WHILE; SELECT _id FROM temp_child_list ORDER BY _level; DROP TABLE IF EXISTS temp_child_list; END; -- 方法3，存儲過程+臨時表 -- 存儲過程實現mysql遞歸查詢，類似oracle start with connect by prior -- 注意 call getChildOrg('00010'); -- 遞歸有深度限制，最大是255層 -- 此存儲過程利用了臨時表 DROP PROCEDURE IF EXISTS showChildList; CREATE PROCEDURE showChildList (IN rootId VARCHAR(32)) BEGIN SET@@max_sp_recursion_depth=99; DROP TEMPORARY TABLE IF EXISTS temp_child_list; CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT ); -- 插入當前節點 INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1); -- 插入子節點 CALL createChildList (rootId, 0); SELECT o.org_id FROM temp_child_list t, a_sys_org o WHERE t._id = o.org_id ORDER BY t.sno; END; DROP PROCEDURE IF EXISTS createChildList; CREATE PROCEDURE createChildList ( IN rootId VARCHAR (32), IN depth_var INT ) BEGIN DECLARE done INT DEFAULT 0; DECLARE b VARCHAR (32); DECLARE cur1 CURSOR FOR SELECT org_id FROM a_sys_org WHERE parent_org_id = rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; INSERT INTO temp_child_list (_id, _depth) SELECT org_id, depth_var FROM a_sys_org WHERE parent_org_id = rootId; OPEN cur1; FETCH cur1 INTO b; WHILE done = 0 DO CALL createChildList (b, depth_var + 1); FETCH cur1 INTO b; END WHILE; CLOSE cur1; END;
