基于条件的行组合选择- MySQL

Question

我有一个工作的SQL查询，它返回以下结果：

​

​

我想要做的是让MySQL计算权重列的sum，并从上面的sum(Weight) <= 300表中给出行的组合。使用上表的预期结果的一个例子是：

​

​

我对此提出的问题是:这是否可能来自MySQL？我是否需要执行多个SQL查询，如何才能产生上述结果？是否有可能从一个查询实现第一个表和组合？

Answer 1

免责声明:我不确定您如何准确地设想从一个查询返回3个结果集，以及为什么只有三个结果集-- (1,4)和(2,3)也是有效的组合。所以，我假设这只是一个普通的例子，您希望完全结果在中得到一些形式。

假设您有这个表(为了使其更通用，我添加了一行，您的示例只会产生两个元素的组合)：

MariaDB [test]> SELECT * FROM t1;
+------+--------+
| id   | weight |
+------+--------+
|    1 |    100 |
|    2 |    120 |
|    3 |    200 |
|    4 |     96 |
|    5 |     50 |
+------+--------+
5 rows in set (0.00 sec)

使用MariaDB 10.2，您可以使用递归CTE来实现您的目标。

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;

你会得到这个：

+-------+------------+-----------+
| 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)

上面的查询并不完美，只是想给出一个可能的解决方案。结果似乎是正确的，你可以根据你的需要改进和完善表现力。

对于第二个问题，“是否有可能从一个查询中实现第一个表和组合？”，您没有说明如何得到第一个表，所以很难给出一个精确的示例，但无论如何，这是可能的。最明显的方法是使用任何用于获取结果集的查询，将其包装到视图中，然后在上面的示例中使用此视图而不是t1表。