如何编写SQL查询无法放入20×14×10容器的可翻转商品

逻辑说明 #

你原来写的6种排列组合的判断逻辑是有效的，确实覆盖了所有翻转场景，但写法非常冗余，后期维护成本很高，容器尺寸调整时需要修改多处条件，更简洁的方案就是你提到的尺寸排序后对比的思路，不过你对判断条件的描述有小误差，正确逻辑如下：

• 先将容器的20×14×10三个尺寸从小到大排序，得到三个基准值：最小基准=10、中间基准=14、最大基准=20
• 商品可放入容器的充要条件：商品的最小边长 ≤ 最小基准，且商品的中间边长 ≤ 中间基准，且商品的最大边长 ≤ 最大基准
• 因此需要筛选的「无法放入」的条件，就是对上述可放入条件取反即可。

SQL实现方案 #

绝大多数主流SQL引擎（MySQL、PostgreSQL、Oracle、Spark SQL等）都支持LEAST（取多个值的最小值）、GREATEST（取多个值的最大值）函数，三个尺寸的中间值可以通过「三个尺寸求和减去最大值减去最小值」的方式计算，优化后的查询代码如下：

select 
  items.bin_id,
  items.bin_type_name,    
  items.bin_usage_name,    
  items.isbn,
  items.boo,
  items.quantity,
  ma.pkg_height,
  ma.pkg_width,  
  ma.pkg_length,    
  ma.pkg_weight,
  items.owner
from items 
left join BOOKER.D_MP_PHYSICAL_ITEMS ma
  on items.boo = ma.asin
where 
  -- 排除可放入容器的商品，保留无法放入的
  NOT (
    LEAST(ma.pkg_length, ma.pkg_width, ma.pkg_height) <= 10
    AND (ma.pkg_length + ma.pkg_width + ma.pkg_height - LEAST(ma.pkg_length, ma.pkg_width, ma.pkg_height) - GREATEST(ma.pkg_length, ma.pkg_width, ma.pkg_height)) <=14
    AND GREATEST(ma.pkg_length, ma.pkg_width, ma.pkg_height) <=20
  )
  -- 如果需要保留没有匹配到尺寸数据的商品，可取消注释下方条件
  -- OR ma.asin IS NULL

兼容说明 #

如果你使用的SQL引擎不支持LEAST/GREATEST函数，可以用CASE WHEN手动实现最大/最小值计算，以最大值为例：

CASE 
  WHEN pkg_length >= pkg_width AND pkg_length >= pkg_height THEN pkg_length
  WHEN pkg_width >= pkg_length AND pkg_width >= pkg_height THEN pkg_width
  ELSE pkg_height 
END

最小值的计算逻辑同理，只需把判断条件里的>=替换为<=即可。

内容的提问来源于stack exchange，提问作者cpbr