创建 根据父节点查询子节点的function
DELIMITER $$
USE `dpp_new`$$
DROP FUNCTION IF EXISTS `getChildLst`$$
CREATE DEFINER=`tag`@`localhost` FUNCTION `getChildLst`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =CAST(rootId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(area_id) INTO sTempChd FROM com_area WHERE FIND_IN_SET(up_area_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;
创建 根据子节点查询父节点的function
DELIMITER $$
USE `dpp_new`$$
DROP FUNCTION IF EXISTS `getParentList`$$
CREATE DEFINER=`tag`@`localhost` FUNCTION `getParentList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
DECLARE sParentList VARCHAR(1000);
DECLARE sParentTemp VARCHAR(1000);
SET sParentTemp =CAST(rootId AS CHAR);
WHILE sParentTemp IS NOT NULL DO
IF (sParentList IS NOT NULL) THEN
SET sParentList = CONCAT(sParentTemp,',',sParentList);
ELSE
SET sParentList = CONCAT(sParentTemp);
END IF;
SELECT GROUP_CONCAT(up_area_id) INTO sParentTemp FROM com_area WHERE FIND_IN_SET(area_id,sParentTemp)>0;
END WHILE;
RETURN sParentList;
END$$
DELIMITER ;
验 证查询:
SELECT getChildLst(1100);
SELECT * FROM com_area WHERE FIND_IN_SET(area_id, getChildLst(1300));
SELECT * FROM com_area WHERE FIND_IN_SET(area_id, getParentList(1310));