How to Calculate Total Quantity Up a Tree Structure in MySQL using a Recursive Stored Procedure?

Determining Total Quantity using Recursive Stored Procedure in MySQL

To cater to the need for a recursive stored procedure in MySQL that computes the total quantity up a tree-like structure in a table, you can employ the following query:

DELIMITER $$

CREATE PROCEDURE calctotal(
   IN number INT,
   OUT total INT
)

BEGIN

   DECLARE parent_ID INT DEFAULT NULL ;
   DECLARE tmptotal INT DEFAULT 0;
   DECLARE tmptotal2 INT DEFAULT 0;

   SELECT parentid   FROM test   WHERE id = number INTO parent_ID;   
   SELECT quantity   FROM test   WHERE id = number INTO tmptotal;     

   IF parent_ID IS NULL
    THEN
    SET total = tmptotal;
   ELSE     
    CALL calctotal(parent_ID, tmptotal2);
    SET total = tmptotal2 * tmptotal;   
   END IF;

END$$

DELIMITER ;

This stored procedure initializes variables for the parent ID and temporary totals. It then retrieves the necessary information from the "test" table. If the parent ID is null, the procedure sets the total to the current quantity. Otherwise, it recursively calls itself with the parent ID and accumulates the total accordingly.

To call the procedure, you need to set certain session variables:

SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255; 

CALL calctotal(6, @total);
SELECT @total;

Replacing the "6" with your desired ID will provide you with the computed total quantity from the root node往上.

The above is the detailed content of How to Calculate Total Quantity Up a Tree Structure in MySQL using a Recursive Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!