如何为每种产品找到合适尺寸的盒子？

很抱歉重新上传这个问题，但我真的想要答案。

请允许我再次问这个问题并希望您的支持。

问题是找到合适尺寸的盒子，让物流企业在运输时节省资金。

我们有两张桌子，分别是盒子和产品。

盒子表包含每个盒子的 ID 和尺寸。 “w”代表宽度，“d”代表深度，“h”代表高度。为了方便起见，请假设我们只有 3 盒样品。

产品表还包括产品 ID、尺寸。维度与盒子表的含义相同。 “可平放”是指产品不仅可以平放包装，还可以平放包装。例如，产品“g”是易碎瓶子，无法水平放置在盒子中。因此，这是可放置列中的“n”。

本题需要查询每个产品ID的大小合适的框。 合适尺寸的盒子意味着产品需要使用空间最小的盒子来运输。

希望您的好心帮助。谢谢。

boxes:

产品：

预期输出：

创建和填充测试表的语句：

create table boxes
    ( box_size char(1) primary key
    , w        number  not null
    , d        number  not null
    , h        number  not null
    )
;

insert into boxes (box_size, w, d, h) values ('S', 353, 250,  25);
insert into boxes (box_size, w, d, h) values ('M', 450, 350, 160);
insert into boxes (box_size, w, d, h) values ('L', 610, 460, 460);

create table products
    ( id      varchar2(10) primary key
    , w       number       not null
    , d       number       not null
    , h       number       not null
    , layable char(1)      check(layable in ('y', 'n'))
    )
;

insert into products (id, w, d, h, layable) values ('a', 350, 250,  25, 'y');
insert into products (id, w, d, h, layable) values ('b', 450, 250, 160, 'y');
insert into products (id, w, d, h, layable) values ('c', 510, 450, 450, 'y');
insert into products (id, w, d, h, layable) values ('d', 350, 250,  25, 'y');
insert into products (id, w, d, h, layable) values ('e', 550, 350, 160, 'y');
insert into products (id, w, d, h, layable) values ('f', 410, 400, 430, 'n');
insert into products (id, w, d, h, layable) values ('g', 350, 240,  25, 'n');
insert into products (id, w, d, h, layable) values ('h', 450, 350, 160, 'n');
insert into products (id, w, d, h, layable) values ('i', 310, 360, 430, 'n');
insert into products (id, w, d, h, layable) values ('j', 500, 500, 600, 'y');    

commit;

当然，关键是两个表之间的联接。我首先单独展示它，而不是完整的查询，以帮助理解。对于每件物品，我们都会找到可容纳该物品的所有盒子尺寸。

在所有情况下，如果产品高度 rotated以适合盒子，无论它们是否可放置）。

仅对于可放置的产品，我们可以在所有三个维度上旋转产品以将其装入盒子中。这意味着，仅对于可铺设产品，我们可以将产品宽度或深度与盒子高度进行比较，并将产品的其余两个尺寸与盒子宽度和深度进行比较。

一旦我们理解了我刚才所说的（就像我们在没有计算机的情况下，只需用铅笔在纸上做这件事的方式一样），翻译成代码几乎是自动的：

select p.id, b.box_size
from   products p left outer join boxes b
       on
            p.h <= b.h and least   (p.w, p.d) <= least   (b.w, b.d)
                       and greatest(p.w, p.d) <= greatest(b.w, b.d)
       or
       p.layable = 'y'
          and
          ( p.w <= b.h and least   (p.h, p.d) <= least   (b.w, b.d)
                       and greatest(p.h, p.d) <= greatest(b.w, b.d)
            or
            p.d <= b.h and least   (p.w, p.h) <= least   (b.w, b.d)
                       and greatest(p.w, p.h) <= greatest(b.w, b.d)
          )
;

Output:

ID  BOX_SIZE
--- --------
a   S       
a   M       
a   L       
b   M       
b   L       
c   L       
d   S       
d   M       
d   L       
e   L       
f   L       
g   S       
g   M       
g   L       
h   M       
h   L       
i   L       
j      

对于每种产品，我们找到了所有合适的尺寸。

请注意查询中的外连接，以包含不适合任何盒子尺寸的产品；产品就是这样j，它出现在输出的末尾。请注意，我使用null作为“的标记无法使用” - “不可用”一词不会比简单使用添加任何有价值的信息null.

下一步是一个简单的聚合 - 对于每个产品，找到有效的最小尺寸。最好的工具是FIRST聚合函数（如下所用）。我们必须按盒子尺寸订购；由于尺寸为 S、M、L（无意中按字母顺序相反排列），所以我使用decode()函数将 1 分配给 S、2 分配给 M、3 分配给 L。聚合查询查找适用于每个产品的“第一个”尺寸。

这里重要的是，查询可以轻松地推广到任意数量的可能的“盒子大小” - 即使并非所有三个维度都按升序排列。 （您也可以拥有只有一个尺寸很大而其他尺寸很小的盒子，等等）。您可以按箱量订购，也可以存放在盒子表中优先顺序，相当于我在查询中所做的decode()功能。

最后，查询和输出如下所示。请注意，我使用了nvl() in the select子句生成'not available'对于最后一项，以防万一您真的需要它（我对此表示怀疑，但这不是我的业务问题。）

select p.id, 
       nvl(  min(b.box_size) keep (dense_rank first 
             order by decode(b.box_size, 'S', 1, 'M', 2, 'L', 3))
          , 'not available') as box_size
from   products p left outer join boxes b
       on
            p.h <= b.h and least   (p.w, p.d) <= least   (b.w, b.d)
                       and greatest(p.w, p.d) <= greatest(b.w, b.d)
       or
       p.layable = 'y'
          and
          ( p.w <= b.h and least   (p.h, p.d) <= least   (b.w, b.d)
                       and greatest(p.h, p.d) <= greatest(b.w, b.d)
            or
            p.d <= b.h and least   (p.w, p.h) <= least   (b.w, b.d)
                       and greatest(p.w, p.h) <= greatest(b.w, b.d)
          )
group  by p.id
;

ID  BOX_SIZE
--- --------
a   S       
b   M       
c   L       
d   S       
e   L       
f   L       
g   S       
h   M       
i   L       
j   not available