祖先树统计
DDL
CREATE TABLE organization_ancestor_id_tree (
id BIGINT NOT NULL COMMENT '对应:smarthse_supervise.organization.id' PRIMARY KEY,
ancestor_id_tree VARCHAR(100) NULL COMMENT 'id的祖先id树(最近祖先在最左,最远祖先在最右)'
) COMMENT '存放每一个:smarthse_supervise.organization.id,的祖先id树,每日03:00更新。';
PROCEDURE
DELIMITER $$
CREATE PROCEDURE generate_all_organization_ancestor_id_tree()
BEGIN
/* 定义变量用于遍历所有监管ID */
DECLARE done BIGINT DEFAULT FALSE;
DECLARE sid BIGINT;
DECLARE current_id BIGINT;
DECLARE prev_id BIGINT;
DECLARE cur CURSOR FOR SELECT id FROM organization;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/* 重复执行此存储过程等价于:以最新的数据重新追溯监管id的祖先id树。 */
TRUNCATE organization_ancestor_id_tree;
/* 创建一个临时表来存储祖先ID */
CREATE TEMPORARY TABLE temp_organization_ancestor_id_list (
id BIGINT
);
/* 打开游标 */
OPEN cur;
/* 遍历游标 */
read_loop:
LOOP
FETCH cur INTO sid;
IF done THEN LEAVE read_loop; END IF;
SET current_id = sid;
SET prev_id = -1;
/* 循环检索,获取每个ID的所有祖先 */
WHILE current_id IS NOT NULL AND current_id != prev_id
DO
INSERT INTO temp_organization_ancestor_id_list (id) VALUES (current_id);
SET prev_id = current_id;
SELECT pid
INTO current_id
FROM organization
WHERE id = current_id AND pid IS NOT NULL AND pid != 999999999999;
END WHILE;
/* 输出所有祖先ID,以逗号分隔 */
INSERT INTO organization_ancestor_id_tree
SELECT sid, GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ',') AS ancestor
FROM temp_organization_ancestor_id_list;
TRUNCATE temp_organization_ancestor_id_list;
/* 重置done值 */
SET done = FALSE;
END LOOP;
/* 关闭游标 */
CLOSE cur;
/* 删除临时表 */
DROP TEMPORARY TABLE IF EXISTS temp_organization_ancestor_id_list;
END$$
DELIMITER ;
CALL
CALL generate_all_organization_ancestor_id_tree();
DJOSIMON