Central America /Earth/North America/Island Nations /Earth/North America/United States /Earth/North America/United States/Alabama /Earth/North America/United States/Alaska /Earth/North America/United States/Arizona /Earth/North America/United States/Arkansas /Earth/North America/United States/California 已选择16行。 已用时间: 00: 00: 00.01
mysql> SELECT COUNT(DISTINCT IFNULL(parentid,-1)) AS LEVEL FROM country_relation ; +-------+ | LEVEL | +-------+ | 4 | +-------+ 1 row in set (0.00 sec)
mysql> SELECT b.`name` AS root_node FROM -> ( -> SELECT id FROM country_relation WHERE parentid IS NULL -> ) AS a, country AS b WHERE a.id = b.id; +-----------+ | root_node | +-----------+ | Earth | +-----------+ 1 row in set (0.00 sec)
mysql> SELECT b.`name` AS leaf_node FROM -> ( -> SELECT id FROM country_relation WHERE id NOT IN (SELECT IFNULL(parentid, -1) FROM country_relation) -> ) AS a, country AS b WHERE a.id = b.id; +-----------------+ | leaf_node | +-----------------+ | South America | | Europe | | Asia | | Africa | | Australia | | Canada | | Central America | | Island Nations | | Alabama | | Alaska | | Arizona | | Arkansas | | California | +-----------------+ 13 rows in set (0.00 sec)
DELIMITER $$ USE `t_girl`$$ DROP PROCEDURE IF EXISTS `sp_show_list`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`() BEGIN -- Created by ytt 2014/11/04. -- Is equal to oracle's connect by syntax. -- Body. DROP TABLE IF EXISTS tmp_country_list; CREATE TEMPORARY TABLE tmp_country_list (node_level INT UNSIGNED NOT NULL, node_path VARCHAR(1000) NOT NULL); -- Get the root node. INSERT INTO tmp_country_list SELECT 1, CONCAT('/',id) FROM country_relation WHERE parentid IS NULL; -- Loop within all parent node. cursor1:BEGIN DECLARE done1 INT DEFAULT 0; DECLARE i1 INT DEFAULT 1; DECLARE v_parentid INT DEFAULT -1; DECLARE v_node_path VARCHAR(1000) DEFAULT ''; DECLARE cr1 CURSOR FOR SELECT parentid FROM country_relation WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid ASC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; OPEN cr1; loop1:LOOP FETCH cr1 INTO v_parentid; IF done1 = 1 THEN LEAVE loop1; END IF; SET i1 = i1 + 1; label_path:BEGIN DECLARE done2 INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1; -- Get the upper path. SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path; -- Escape the outer not found exception. IF done2 = 1 THEN SET done2 = 0; END IF; INSERT INTO tmp_country_list SELECT i1,CONCAT(IFNULL(v_node_path,''),'/',id) FROM country_relation WHERE parentid = v_parentid; END; END LOOP; CLOSE cr1; END; -- Update node's id to its real name. update_name_label:BEGIN DECLARE cnt INT DEFAULT 0; DECLARE i2 INT DEFAULT 0; SELECT MAX(node_level) FROM tmp_country_list INTO cnt; WHILE i2 < cnt DO UPDATE tmp_country_list AS a, country AS b SET a.node_path = REPLACE(a.node_path,CONCAT('/',b.id),CONCAT('/',b.name)) WHERE LOCATE(CONCAT('/',b.id),a.node_path) > 0; SET i2 = i2 + 1; END WHILE; END; SELECT node_path FROM tmp_country_list; END$$ DELIMITER ;
mysql> CALL sp_show_list(); +-----------------------------------------------+ | node_path | +-----------------------------------------------+ | /Earth | | /Earth/North America | | /Earth/South America | | /Earth/Europe | | /Earth/Asia | | /Earth/Africa | | /Earth/Australia | | /Earth/North America/Canada | | /Earth/North America/Central America | | /Earth/North America/Island Nations | | /Earth/North America/United States | | /Earth/North America/United States/Alabama | | /Earth/North America/United States/Alaska | | /Earth/North America/United States/Arizona | | /Earth/North America/Unit |