I need to select records based on time(in second) listed in a MySQL database table, with a single query (no store procedures). The record set should contain all records where the sum total time equals or if needed exceeds a specific time. (Example, if i want get 4 random record where time(secound) sum is lessthen 30 second)

Table :

 ---- ------- 
| id | times |
 ---- ------- 
|  1 |      8|
|  2 |     20|
|  3 |      1|
|  4 |      3|
|  5 |      2|
|  6 |      6|
|  7 |      9|
|  9 |     15|
| 10 |     12|
| 11 |      8|
 ---- ------- 

Like i want 4 record randomly, it's doesn't matter whis come first output will be like : 1

 ---- ------- 
| id | times |
 ---- ------- 
|  2 |     20|
|  3 |      1|
|  5 |      2|
|  6 |      6|
 ---- ------- 
SUM OF ALL TIME IS 29

2

 ---- ------- 
| id | times |
 ---- ------- 
|  3 |      1|
|  7 |      9|
| 10 |     12|
| 11 |      8|
 ---- ------- 
SUM OF ALL TIME IS 30

3

 ---- ------- 
| id | times |
 ---- ------- 
|  1 |      8|
|  5 |      2|
|  4 |      3|
|  9 |     15|
 ---- ------- 
SUM OF ALL TIME IS 28

Something like that

CodePudding user response：

I think that is a knapsack problem, I don't think mysql can solve it. Here is a reference https://en.wikipedia.org/wiki/Knapsack_problem

CodePudding user response：

Extensive solution for strict sum:

SELECT t1.times, t2.times, t3.times, t4.times
FROM test t1
JOIN test t2 ON t1.id < t2.id
JOIN test t3 ON t2.id < t3.id
JOIN test t4 ON t3.id < t4.id
WHERE t1.times   t2.times   t3.times   t4.times = 30;

fiddle