在 MYSQL 树形结构中，如何统计和更新各个节点的业务数量？

DELIMITER //

CREATE PROCEDURE update_node_num(IN root_id INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE child_id INT;
  DECLARE child_num INT;
  DECLARE cur CURSOR FOR SELECT id, num FROM your_table WHERE parentId = root_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO child_id, child_num;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 递归更新子节点
    CALL update_node_num(child_id);

    -- 更新当前节点的num值
    UPDATE your_table SET num = (SELECT SUM(num) FROM your_table WHERE parentId = root_id) WHERE id = root_id;
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;

import java.sql.*;

public class UpdateNodeNum {
    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "user", "password")) {
            CallableStatement statement = connection.prepareCall("{call update_node_num(?)}");
            statement.setInt(1, rootNodeId); // rootNodeId为需要更新的根节点ID
            statement.execute();
            System.out.println("节点业务数量更新成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}