数据库中有一个地区表,表结构如下:
CREATE TABLE `area` (
`area_id` INT(11) NOT NULL AUTO_INCREMENT,
`parent_id` INT(11) NULL DEFAULT NULL,
`name` VARCHAR(60) NULL DEFAULT NULL,
`level` TINYINT(4) NULL DEFAULT NULL,
`aleph` VARCHAR(5) NULL DEFAULT NULL,
`show_order` INT(11) NULL DEFAULT NULL,
`status` TINYINT(4) NULL DEFAULT NULL,
PRIMARY KEY (`area_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
根据一个地区的ID(areaId),其下级地区ID的集合查询如下(其中deep表示递归的深度):
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `queryChildAreaIds`(`areaId` INT, `deep` INT)
RETURNS varchar(1024) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查询指定区域的所有下级的地区ID集'
BEGIN
DECLARE tempIds VARCHAR(512);
DECLARE result VARCHAR(1024);
DECLARE deeps INT;
SET tempIds = areaId;
SET result = '';
SET deeps = deep;
WHILE deeps > 0 AND tempIds IS NOT NULL DO
SET deeps = deeps - 1;
SELECT group_concat(area_id) INTO tempIds from AREA where
FIND_IN_SET(parent_id, tempIds) > 0;
IF tempIds IS NOT NULL THEN
IF LENGTH(result) = 0 THEN
SET result = CONCAT(result, tempIds);
ELSE
SET result = CONCAT(result, ',', tempIds);
END IF;
END IF;
END WHILE;
return result;
END
类似,查询父级ID的集合:
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `queryParentAreaIds`(`areaId` INT, `deep` INT)
RETURNS varchar(256) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查询指定区域的所有上级的地区ID集合'
BEGIN
DECLARE tempId INT;
DECLARE lastId INT;
DECLARE deeps INT;
DECLARE result VARCHAR(256);
SET tempId = areaId;
SET result = '';
SET deeps = deep;
WHILE deeps > 0 AND tempId > 0 DO
SET deeps = deeps - 1;
SET lastId = areaId;
SELECT parent_id INTO tempId FROM AREA where area_id=tempId and `status`=1 limit 1;
IF tempId IS NULL OR lastId = tempId THEN
SET areaId = 0;
ELSEIF LENGTH(result) = 0 THEN
SET result = CONCAT(result, tempId);
ELSE
SET result = CONCAT(result, ',', tempId);
END IF;
END WHILE;
return result;
END