时间戳

首页 > 解决方案 > Oracle PL/sql：计算所有可能数字组合的总和

sql - Oracle PL/sql：计算所有可能数字组合的总和

问题描述

甲骨文数据库

假设我有 3 个盒子，里面有不同数量的物品（盒子的数量是固定的）

盒子编号和数量如下

B101 5
B102 5
B103 4

有一个容器，里面有一些物品

现有数量 - 2

容器容量 - 15

现在我想要box1，box2，box3，现有数量的所有可能组合的总和。

我将只选择 sum < capacity 最适合的组合

所有组合都必须具有现有数量，因为我们不能忽略它

本案例的最终结果

盒子 1 + 盒子 2 + 现有数量 .5+5+2 适合容量 15 。

需要一个 PL/SQL 块来执行相同的活动

我将在记录表中获取盒子 ID 和盒子数量（用户定义）

标签： sqloracleplsqlplsqldeveloper

解决方案

可能有更简单的方法，但我会使用递归 CTE。这是一个简单的 SQL 示例，带有额外的示例行：

create table boxes (id varchar2(4), qty number);

insert into boxes values ('B101',5);
insert into boxes values ('B102',5);
insert into boxes values ('B103',4);
insert into boxes values ('B104',9);
insert into boxes values ('B105',11);
insert into boxes values ('B106',2);
insert into boxes values ('B107',1);

with c (r, id, qty, lvl) as (
    -- anchor query
    select id as r, id, qty, 1 as lvl
    from boxes
    where qty + 2 < 15
    union all
    -- recursive query
    select c.r || ',' || b.id, b.id, b.qty+c.qty, c.lvl+1
    from boxes b
    join c on c.id < b.id
    where b.qty + c.qty + 2 < 15
    )
select r, lvl, qty 
from c
order by qty desc, lvl asc
;

这将显示所有组合，最适合的在顶部。我在级别上添加了二级排序，假设在平局的情况下，您希望每个容器的盒子数量最少。但是您可能更喜欢每个容器的最大盒子数。

我还使用join on c.id < b.id了交叉连接而不是交叉连接，因为我认为您并不是真的想要所有组合，而是想要所有 UNIQUE 组合，所以它更像是树搜索。

以及作为 PL/SQL 函数的示例：

create or replace function fit_boxes(existing_qty in number, capacity in number)
return varchar2
is
    box_list varchar2(4000);
begin

    with c (r, id, qty, lvl) as (
        select id as r, id, qty, 1 as lvl
        from boxes
        where qty + existing_qty < capacity
        union all
        select c.r || ',' || b.id, b.id, b.qty+c.qty, c.lvl+1
        from boxes b
        join c on c.id < b.id
        where b.qty + c.qty + existing_qty < capacity
        )
    select r into box_list
    from c
    order by qty desc, lvl asc
    fetch first 1 row only
    ;

    return box_list;

exception when NO_DATA_FOUND then
  return 'No boxes fit'
end;
/

select fit_boxes(2,15) from dual;

最后，我猜你想要 sum <= capacity，但你的问题肯定是“sum < capacity”，所以我就是这样写的。只需使用您的数据对其进行测试并确保其按预期工作即可。

编辑：当然，为了解释查询逻辑 - 对于递归 CTE，您从锚查询开始并将其联合到递归查询（它不断从 CTE 本身迭代选择）。

对于锚点，我们首先选择所有可以放入容器中的单个框（其中 qty + 2 < 15）。在我们的示例中，它们都适合，所以我们有 7 行，lvl 为 1。

对于递归部分，我们在容器中已经有 1 个或多个框c，我们想看看剩下的哪些框b适合。所以我们加入他们，c.id < b.id用来确保我们只查看b尚未在c. 一旦我们查看了所有的框，连接将返回 0 行并且递归将停止。

对于 CTE 中的 4 列 -r显示到目前为止我们添加到容器中的所有框，id显示最近添加的框的 id（很重要，因此我们可以跟踪我们考虑过的框），qty总结当前容器中所有盒子的大小，并lvl显示容器中有多少盒子。

推荐阅读