代码之家 › 专栏 › 技术社区 › jkelley

一个装箱SQL问题的探讨

sql

jkelley · 技术社区 · 15 年前

我在SQL中有一个问题,我需要从事务列表生成一个装箱单。

数据模型

这些事务存储在一个表中,该表包含:

事务ID
项目ID
项目数量

每个事务可以有多个项目(并且同时有多个具有相同事务ID的行)。每个项目的数量从1到N。

业务问题

业务要求我们创建一个装箱单,装箱单中的每一行项目都包含箱中每个项目的计数。

每个盒子只能容纳160件物品(它们的尺寸/重量恰好相同)。根据订单总数,我们需要将项目拆分为不同的框(有时甚至将单个项目的集合拆分为两个框)

因此,挑战是采用该数据模式并得出结果集,其中包括每个框中每个项所属的数量。

我现在以一些不太漂亮的方式强行推行这一方案,我想知道是否有人有一个优雅/简单的解决方案,我忽略了。

输入/输出示例

我们真的需要隔离每个盒子中每个物品的数量……例如:

订单1:

A项的100
项目B的100
C项140

这将导致结果集中出现三行:

框1:A(100),B(60)
方框2:B(40),C(120)
第3栏:C(20)

理想情况下,这个查询足够聪明,可以将所有的C放在一起,但是在这一点上——我们不太关心这个问题。

5 回复 | 直到 7 年前

Professional Sounding Name 15 年前

像这样的东西怎么样

SELECT SUM([Item quantity]) as totalItems
     , SUM([Item quantity]) / 160 as totalBoxes
     , MOD(SUM([Item Quantity), 160) amountInLastBox
FROM [Transactions]
GROUP BY [Transaction Id]

让我知道你要找的结果集中的哪些字段,我可以想出一个更好的

Community CDub 6 年前

你读过装箱圣经吗?

https://sqlserverfast.com/?s=bin+packing

Michael Buen 15 年前

试着从中挑选一些想法 http://www.postgresonline.com/journal/index.php?/archives/138-Allocating-People-into-Groups-with-SQL-the-Sequel.html

它使用CTE

C B dkretz 7 年前

我在寻找类似的东西,我所能实现的就是将行扩展到事务中的项目计数数,并将它们分组到容器中。不过不是很优雅……而且,因为字符串聚合在SQL Server中仍然很麻烦(Oracle,我想你!)我得把最后一部分删掉。我的意思是把计数放在一排……

我的解决方案如下:

示例事务表:

INSERT INTO transactions
(trans_id, item, cnt) VALUES
('1','A','50'), 
('2','A','140'), 
('3','B','100'), 
('4','C','80');
GO

创建一个虚拟序列表,其中包含从1到1000的数字(我假设单个事务中一个项目允许的最大数字为1000):

CREATE TABLE numseq (n INT NOT NULL IDENTITY) ;
GO
INSERT numseq DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 1000 INSERT numseq DEFAULT VALUES ;
GO

现在我们可以从事务表生成一个临时表,其中每个事务和项目都存在 “CNT” 子查询中的次数,然后使用除法给箱子编号,并按箱子编号分组:

SELECT bin_nr, item, count(*) count_in_bin
INTO result
FROM (
  SELECT t.item, ((row_number() over (order by t.item, s.n) - 1) / 160) + 1 as bin_nr
  FROM transactions t 
  INNER JOIN numseq s
  ON t.cnt >= s.n -- join conditionally to repeat transaction rows "cnt" times
) a
GROUP BY bin_id, item
ORDER BY bin_id, item
GO

结果是:

bin_id item count_in_bin
1      A    160
2      A    30
2      B    100
2      C    30
3      C    50

在Oracle中,最后一步将非常简单:

SELECT bin_id, WM_CONCAT(CONCAT(item,'(',count_in_bin,')')) contents
FROM result
GROUP BY bin_id

Bork Blatt 15 年前

这不是最漂亮的答案,但我正在使用类似的方法通过订单流程跟踪库存项目,这很容易理解,并且可能会导致您开发一种比我更好的方法。

我会创建一个名为“packeditem”的表或类似的表。列将是:

packed_item_id (int) - Primary Key, Identity column
trans_id (int)
item_id (int)
box_number (int)

此表中的每个记录代表将要装运的1个物理单元。

假设有人用第12项的20向事务4添加了一行,我将向packeditem表添加20条记录,所有记录都带有事务ID、项目ID和一个空的框号。如果更新了一行,则需要从packeditem表中添加或删除记录,以便始终存在1:1相关性。

当时间到了,你可以简单地

SELECT TOP 160 FROM PackedItem WHERE trans_id = 4 AND box_number IS NULL

并设置 box_number 在这些记录上转到下一个可用的框号,直到没有记录保留在 博克斯数 是空的。在while循环中使用一个相当复杂的update语句是可能的——我没有时间完全构造它。

您现在可以通过如下查询此表轻松获得所需的装箱单:

SELECT box_number, item_id, COUNT(*) AS Qty
FROM PackedItem
WHERE trans_id = 4
GROUP BY box_number, item_id

优点-易于理解,相当容易实现。陷阱-如果表与事务中的行不同步,最终结果可能是错误的;此表将在其中获得许多记录,并且将是服务器的额外工作。需要索引每个ID字段以保持良好的性能。