如何获得总和为特定值的行的随机组合？

Question

我有的表：

+--------+--------------------+

|item_id|        value        |

+--------+--------------------+

|  1     | 1                  |

|  2     | 4                  |

|  3     | 2                  |

|  4     | 6                  |

+--------+--------------------+

SQL 查询结果应该是什么：总和为 10 的项目的随机组合以及不同项目的可变数量（在本例中为 2。）

+--------------+--------------------+-------------+

|item_id       |        amount      |      sum    |

+--------------+--------------------+-------------+

|2             |2                   |8            |

|3             |1                   |2            |

+--------------+--------------------+-------------+

结果显示

您得到 2 次第 2 项（其值为 4，因此加起来为 8）。

并且您获得一次项目 3（其值为 2）

此组合总计为 10。

当它不应该总是相同的组合并且如果有其他可能性时随机选择，这甚至可能吗？

Answer 1

您可以使用自联接获得所有此类组合：select t1.item_id, t2.item_idfrom t t1 join     t t2     on t1.value + t2.value = 10;这会将值放在列而不是单独的行中。

Answer 2

假设您想要一个随机组合，您可以执行以下操作：select&nbsp; *from (&nbsp; select&nbsp; &nbsp; a.item_id as item1,&nbsp;&nbsp; &nbsp; x.n as amount1,&nbsp;&nbsp; &nbsp; a.value * x.n as sum1,&nbsp; &nbsp; b.item_id as item2,&nbsp;&nbsp; &nbsp; y.n as amount2,&nbsp;&nbsp; &nbsp; b.value * y.n as sum2,&nbsp; &nbsp; rand() as r&nbsp; from my_table a&nbsp; join my_table b on b.item_id <> a.item_id&nbsp; cross join (&nbsp; &nbsp; select 1 as n union select 2 union select 3 union select 4&nbsp;&nbsp; &nbsp; union select 5 union select 6 union select 7 union select 8&nbsp;&nbsp; &nbsp; union select 9 union select 10) x&nbsp; cross join (&nbsp; &nbsp; select 1 as n union select 2 union select 3 union select 4&nbsp; &nbsp; union select 5 union select 6 union select 7 union select 8&nbsp;&nbsp; &nbsp; union select 9 union select 10) y&nbsp; where a.value * x.n + b.value * y.n = 10) zorder by r -- sorted randomlylimit 1 -- to get only one combination; remove to get them all每次运行此查询时，它都会选择一个随机的 [不同] 解决方案。创建您提到的表和数据的脚本（我曾经测试过）是：create table my_table (&nbsp; item_id int,&nbsp; value int);insert into my_table (item_id, value) values (1, 1);insert into my_table (item_id, value) values (2, 4);insert into my_table (item_id, value) values (3, 2);insert into my_table (item_id, value) values (4, 6);2019 年 7 月 1 日编辑：根据要求，这是使用递归 CTE（公用表表达式）的等效 [更短] 解决方案，自 10.2.2 起在 MariaDB 中可用（请参阅递归公用表表达式）：with recursiveval as (select 1 as n union all select n + 1 from val where n < 10)select&nbsp; *from (&nbsp; select&nbsp; &nbsp; a.item_id as item1,&nbsp;&nbsp; &nbsp; x.n as amount1,&nbsp;&nbsp; &nbsp; a.value * x.n as sum1,&nbsp; &nbsp; b.item_id as item2,&nbsp;&nbsp; &nbsp; y.n as amount2,&nbsp;&nbsp; &nbsp; b.value * y.n as sum2,&nbsp; &nbsp; rand() as r&nbsp; from my_table a&nbsp; join my_table b on b.item_id <> a.item_id&nbsp; cross join val x&nbsp; cross join val y&nbsp; where a.value * x.n + b.value * y.n = 10) zorder by r -- sorted randomlylimit 1 -- to get only one combination; remove to get all 22 answers如果您需要使用更高的数字，此解决方案的扩展性会更好。