首先你的项目中肯定有一张可以存储树形数据的数据表,我的项目中的这个是一个机构表,部分数据如下:
CREATE TABLE `wf_unit` (
`UNIT_ID` varchar(64) NOT NULL,
`UNIT_NAME` varchar(128) DEFAULT NULL,
`UNIT_FULLNAME` varchar(256) DEFAULT NULL,
`SUPER_UNITID1` varchar(32) DEFAULT NULL,
……
PRIMARY KEY (`UNIT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
方法一:使用函数获取
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `sysi`.`getChildIdList`(rootId VARCHAR(32) charset utf8)
RETURNS text
BEGIN
DECLARE sTemp text;
DECLARE sTempChd text;
SET@@group_concat_max_len = 102400;
SET sTemp = '$';
SET sTempChd = rootId;
WHILE sTempChd IS NOT NULL DO
SET sTemp = concat(sTemp, ',', sTempChd);
SELECT
group_concat(unit_id) INTO sTempChd
FROM
wf_unit
WHERE
FIND_IN_SET(super_unitid1, sTempChd) > 0;
END WHILE;
SET@@group_concat_max_len = 1024;
RETURN SUBSTRING(sTemp,3);
END$$
DELIMITER ;
在Mysql中的查询语句是这样的(说明:90010401是某个机构下的一个人的Unit_id,90是这个人的super_unitid1):
SELECT * from wf_unit where FIND_IN_SET('90010401',(select getChildIdList('90')))
方法二:使用存储过程和临时表
showChildList存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `sysi`.`showChildList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `showChildList`(IN rootId VARCHAR(32) charset utf8)
BEGIN
SET@@max_sp_recursion_depth=99;
DROP TEMPORARY TABLE IF EXISTS temp_child_list;
CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT );
-- 插入当前节点
INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1);
-- 插入子节点
CALL createChildList (rootId, 0);
SELECT w.UNIT_ID FROM temp_child_list t, wf_unit w WHERE t._id = w.UNIT_ID ORDER BY t.sno;
END$$
DELIMITER ;
createChildList存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `sysi`.`createChildList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createChildList`(IN rootId VARCHAR (32), IN depth_var INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR (32);
DECLARE cur1 CURSOR FOR SELECT UNIT_ID FROM wf_unit WHERE SUPER_UNITID1 = rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
INSERT INTO temp_child_list (_id, _depth) SELECT UNIT_ID, depth_var FROM wf_unit WHERE SUPER_UNITID1= rootId;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done = 0 DO CALL createChildList (b, depth_var + 1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END$$
DELIMITER ;
在Mysql中的查询语句是这样的(说明:90是这个人的unit_id):
call showChildList('90');