Choosing a combination of rows based on a condition - MySQL
I have a working SQL query that returns the following results:
What I want to do is MySQL to calculate the sum
weight column and represent the combinations of rows from the above table where sum(Weight) <= 300
. An example of expected results using the above table would be:
My questions about this: Is this possible from MySQL? Do I need to execute multiple SQL queries and how can I present the results illustrated above? Is it possible to get the first table and combinations from one query?
source to share
Disclaimer: I'm not sure exactly how you intend to return 3 result sets from one query, and why there are only three - (1,4) and (2,3) would also be valid combinations. So, I assume this is just a general example and you want the complete result of some .
Let's say you have this table (I added one line to make it more general, for example you only create 2-element combinations):
MariaDB [test]> SELECT * FROM t1;
+------+--------+
| id | weight |
+------+--------+
| 1 | 100 |
| 2 | 120 |
| 3 | 200 |
| 4 | 96 |
| 5 | 50 |
+------+--------+
5 rows in set (0.00 sec)
As of MariaDB 10.2, you can use recursive CTE to achieve your goal, for example
WITH RECURSIVE comb(id,ids,weights,sumweight) AS (
SELECT
id,
CAST(t1.id AS CHAR) AS ids,
CAST(weight AS CHAR) AS weights,
weight AS sumweight
FROM t1
WHERE weight <= 300
UNION
SELECT
t1.id AS id,
CONCAT(comb.ids,',',t1.id) AS ids,
CONCAT(comb.weights,',',weight) AS weights,
t1.weight + comb.sumweight AS sumweight
FROM t1 JOIN comb ON (comb.id < t1.id)
HAVING sumweight <= 300
) SELECT ids, weights, sumweight FROM comb;
You will get this:
+-------+------------+-----------+
| ids | weights | sumweight |
+-------+------------+-----------+
| 1 | 100 | 100 |
| 2 | 120 | 120 |
| 3 | 200 | 200 |
| 4 | 96 | 96 |
| 5 | 50 | 50 |
| 1,2 | 100,120 | 220 |
| 1,3 | 100,200 | 300 |
| 1,4 | 100,96 | 196 |
| 1,5 | 100,50 | 150 |
| 2,4 | 120,96 | 216 |
| 2,5 | 120,50 | 170 |
| 3,4 | 200,96 | 296 |
| 3,5 | 200,50 | 250 |
| 4,5 | 96,50 | 146 |
| 1,2,5 | 100,120,50 | 270 |
| 1,4,5 | 100,96,50 | 246 |
| 2,4,5 | 120,96,50 | 266 |
+-------+------------+-----------+
17 rows in set (0.00 sec)
The above query is not perfect, it just gives an idea of ââa possible solution. The result appears to be correct and you can improve and polish the performance to suit your needs.
For your second question, "Is it possible to get the first table and combinations from the same query?", You did not say how you got the first table, so it is difficult to give an exact example, but in any case it should certainly be possible. The most obvious way is to take whatever query you used to get this result set, wrap it in a view, and then use that view instead of the table t1
in the example above.
source to share