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

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_id

from t t1 join

     t t2

     on t1.value + t2.value = 10;

这会将值放在列而不是单独的行中。

Answer 2

假设您想要一个随机组合，您可以执行以下操作：

select

  *

from (

  select

    a.item_id as item1, 

    x.n as amount1, 

    a.value * x.n as sum1,

    b.item_id as item2, 

    y.n as amount2, 

    b.value * y.n as sum2,

    rand() as r

  from my_table a

  join my_table b on b.item_id <> a.item_id

  cross join (

    select 1 as n union select 2 union select 3 union select 4 

    union select 5 union select 6 union select 7 union select 8 

    union select 9 union select 10) x

  cross join (

    select 1 as n union select 2 union select 3 union select 4

    union select 5 union select 6 union select 7 union select 8 

    union select 9 union select 10) y

  where a.value * x.n + b.value * y.n = 10

) z

order by r -- sorted randomly

limit 1 -- to get only one combination; remove to get them all

每次运行此查询时，它都会选择一个随机的 [不同] 解决方案。

创建您提到的表和数据的脚本（我曾经测试过）是：

create table my_table (

  item_id int,

  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 recursive

val as (select 1 as n union all select n + 1 from val where n < 10)

select

  *

from (

  select

    a.item_id as item1, 

    x.n as amount1, 

    a.value * x.n as sum1,

    b.item_id as item2, 

    y.n as amount2, 

    b.value * y.n as sum2,

    rand() as r

  from my_table a

  join my_table b on b.item_id <> a.item_id

  cross join val x

  cross join val y

  where a.value * x.n + b.value * y.n = 10

) z

order by r -- sorted randomly

limit 1 -- to get only one combination; remove to get all 22 answers

如果您需要使用更高的数字，此解决方案的扩展性会更好。