Mysql 遞迴查詢

標籤：arc   pre   inno   _id   http   方式   rem   drop   ret   

Mysql 遞迴查詢

 定義：父子查詢： 根據父 id 查詢下面所有子節點資料；子父查詢： 根據子 id 查詢上面所有父節點資料；

1 建立表

DROP TABLE IF EXISTS `shop`;CREATE TABLE `shop` ( `shop_id` int(11) NOT ‘0‘ AUTO_INCREMENT, `parent_id` int(11) DEFAULT ‘0‘, `name` varchar(255) DEFAULT ‘0‘, PRIMARY KEY (`shop_id`)) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

2 初始化資料：

INSERT INTO `shop` VALUES (‘1‘, ‘0‘, ‘總店‘;INSERT INTO `shop` VALUES (‘2‘, ‘1‘, ‘分店1‘;INSERT INTO `shop` VALUES (‘3‘, ‘1‘, ‘分店2‘);INSERT INTO `shop` VALUES (‘4‘, ‘1‘, ‘分店3‘);INSERT INTO `shop` VALUES (‘5‘, ‘1‘, ‘分店4‘);INSERT INTO `shop` VALUES (‘6‘, ‘1‘, ‘分店5‘);INSERT INTO `shop` VALUES (‘7‘, ‘1‘, ‘分店6‘);

3 向下遞迴

利用find_in_set()函數和group_concat()函數實現遞迴查詢：

DROP FUNCTION IF EXISTS queryChildrShop;CREATE FUNCTION queryChildrShop(shopid INT)RETURNS VARCHAR(4000)BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd VARCHAR(4000);SET sTemp=‘‘;SET sTempChd = CAST(shopid AS CHAR);WHILE sTempChd IS NOT NULL DOSET sTemp= CONCAT(sTemp,‘,‘,sTempChd);SELECT GROUP_CONCAT(shop_id) INTO sTempChd FROM shop WHERE FIND_IN_SET(parent_Id,sTempChd)>0;END WHILE;RETURN sTemp;END;

4 、調用方法：

select shop_id,parent_id,name from shop where FIND_IN_SET(shop_id,queryChildShop(#{shopId}))

 查詢說有父節點為1的門店資訊

 

5  向上遞迴

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)RETURNS VARCHAR(4000)BEGINDECLARE sTemp VARCHAR(4000);DECLARE sTempChd VARCHAR(4000);SET sTemp=‘$‘;SET sTempChd = CAST(areaId AS CHAR);SET sTemp = CONCAT(sTemp,‘,‘,sTempChd);SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;WHILE sTempChd <> 0 DOSET sTemp = CONCAT(sTemp,‘,‘,sTempChd);SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;END WHILE;RETURN sTemp;END;

6、調用方式：

select shop_id,parent_id,name from shop where FIND_IN_SET(shop_id,queryChildrenAreaInfo1(#{shopId}))

查詢id為"7"的節點的所有上級節點：

 

 

Mysql 遞迴查詢