在MYSQL中如何統(tǒng)計(jì)樹形結(jié)構(gòu)的業(yè)務(wù)數(shù)量及其更新策略？

--  計(jì)算指定節(jié)點(diǎn)及其子節(jié)點(diǎn)的總?cè)丝跀?shù)量 DELIMITER // CREATE PROCEDURE CalculateTotalPopulation(IN nodeId INT, OUT totalPopulation INT) BEGIN   DECLARE finished INTEGER DEFAULT 0;   DECLARE childId INT;   DECLARE populationCursor CURSOR FOR SELECT id FROM your_table WHERE parentId = nodeId;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;    SET totalPopulation = (SELECT num FROM your_table WHERE id = nodeId);    OPEN populationCursor;   read_loop: LOOP     FETCH populationCursor INTO childId;     IF finished = 1 THEN       LEAVE read_loop;     END IF;     CALL CalculateTotalPopulation(childId, @childPopulation);     SET totalPopulation = totalPopulation + @childPopulation;   END LOOP;   CLOSE populationCursor; END // DELIMITER ;

DELIMITER // CREATE TRIGGER update_parent_population AFTER UPDATE ON your_table FOR EACH ROW BEGIN   IF NEW.type = 3 THEN     CALL UpdateParentPopulation(NEW.id);   END IF; END // DELIMITER ;  -- 更新父節(jié)點(diǎn)人口數(shù)量的存儲(chǔ)過(guò)程 (需根據(jù)實(shí)際表結(jié)構(gòu)修改) DELIMITER // CREATE PROCEDURE UpdateParentPopulation(IN nodeId INT) BEGIN   DECLARE parentId INT;   SET parentId = (SELECT parentId FROM your_table WHERE id = nodeId);   WHILE parentId IS NOT NULL DO     UPDATE your_table SET num = (SELECT SUM(num) FROM your_table WHERE parentId = parentId) WHERE id = parentId;     SET parentId = (SELECT parentId FROM your_table WHERE id = parentId);   END WHILE; END // DELIMITER ;