I have the following records in a table:

Let's say i have an total of 596.69 which turns out to be the sum of rows 1, 4, and 5.

Is it possible to have a query that would scan all records of the Amount column and return the rows that sums the total of 596.69?

CodePudding user response：

For MySQL 8 :

WITH RECURSIVE
cte AS ( SELECT InvoiceNumber, 
                Amount, 
                InvoiceNumber InvoiceNumbers, 
                Amount currentAmount 
         FROM test
       UNION ALL
         SELECT test.InvoiceNumber,
                test.Amount,
                CONCAT(cte.InvoiceNumbers, ',', test.InvoiceNumber),
                cte.currentAmount   test.Amount
         FROM test
         JOIN cte ON test.Amount < cte.Amount
                 AND cte.currentAmount   test.Amount <= @sum )
SELECT InvoiceNumbers 
FROM cte
WHERE currentAmount = @sum;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2444f918f79d0c05ffa98d26416e43a3

In MySQL 5.x do the same in stored procedure.