MySQL版的树型查询

NND，捣鼓了好长时间才捣鼓出来的MySQL树型查询。效率可能有点低。查询时间比较长。




DROP PROCEDURE P_QUERY_EQUIPMENT_LIST;

DROP PROCEDURE P_GET_EQUIPMENT_CHILDER;

create PROCEDURE P_QUERY_EQUIPMENT_LIST(IN rootId INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tempList(sid int);
CALL P_GET_EQUIPMENT_CHILDER(rootId);
select sid from tempList where sid in (select id from equipment where node_level = 5);
drop temporary table if exists tempList;
END


CREATE PROCEDURE P_GET_EQUIPMENT_CHILDER(IN rootId INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE curl CURSOR FOR SELECT id FROM equipment where parent_id = rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
INSERT into tempList VALUES(rootId);
SET @@max_sp_recursion_depth = 10;
OPEN curl;
FETCH curl INTO b;
WHILE done = 0 DO
CALL P_GET_EQUIPMENT_CHILDER(b);
FETCH curl INTO b;
END WHILE;
CLOSE curl;

END;