sql - 计算一组给定的项目(重量)所需的盒子数量

Question

我有一个 ms-sql 表，看起来像这样(重量 = kg)。

我希望能够计算给定标识符所需的箱子数量和每个箱子的重量。一个盒子最多可以容纳 30 公斤。该标识符的所有项目都可以在一个框中混合。我仅限于 sql (2008)，但一切都可以使用(CTE、Functions、StoredProcs 等等)。我尝试了不同的方法(CTE、函数)，但无法获得正确的结果。任何形式的帮助表示赞赏。

预期输出

选择标识符100001时:

选择标识符100002时:

选择标识符100003时:

选择标识符100004时:

更新

示例表

CREATE TABLE [dbo].[tblTest](
    [position] [int] NOT NULL,
    [item] [varchar](31) NOT NULL,
    [quantity] [money] NOT NULL,
    [weight] [money] NOT NULL,
    [identifier] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED 
(
    [position] ASC,
    [identifier] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

样本数据

INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (1, N'0000001', 4.0000, 10.0000, N'100001')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (1, N'0000003', 3.0000, 15.0000, N'100002')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (1, N'0000006', 7.0000, 25.0000, N'100003')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (1, N'0000007', 1.0000, 1.5000, N'100004')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (1, N'0023021', 2.0000, 14.5000, N'100005')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (2, N'0000002', 1.0000, 15.0000, N'100001')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (2, N'0000004', 1.0000, 5.0000, N'100002')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (2, N'0000008', 1.0000, 2.5000, N'100004')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (2, N'0023022', 3.0000, 17.5000, N'100005')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (3, N'0000005', 3.0000, 2.5000, N'100002')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (3, N'0000009', 3.0000, 6.0000, N'100004')
INSERT [dbo].[tblTest] ([position], [item], [quantity], [weight], [identifier]) VALUES (4, N'0000010', 1.0000, 1.0000, N'100004')

Answer 1

这是一个选项:

在此处查看演示:http://rextester.com/THP2733

设置:

create table tbl
(position integer, item integer, quantity integer, weight decimal(10,2), identifier integer);

insert into tbl
    select 1,  1, 4,  10, 100001 union all
    select 2,  2, 1,  15, 100001 union all
    select 1,  3, 3,  15, 100002 union all
    select 2,  4, 1,   5, 100002 union all
    select 3,  5, 3, 2.5, 100002 union all
    select 1,  6, 7,  25, 100003 union all
    select 1,  7, 1, 1.5, 100004 union all
    select 2,  8, 1, 2.5, 100004 union all
    select 3,  9, 3,   6, 100004 union all
    select 4, 10, 1,   1, 100004 ;

查询:

with cte(position, item, quantity, weight, identifier, cntr)
as(select position, item, quantity, weight, identifier, quantity
   from tbl t1  
   union all   
   select t2.position, t2.item, t2.quantity, t2.weight, t2.identifier, cte.cntr - 1
   from tbl t2
   join cte 
     on t2.identifier = cte.identifier
    and t2.item = cte.item
    and cte.cntr > 1
)

select  
identifier,
sum(flg) over (partition by identifier order by item, cntr desc) package,
case when rolling_weight - lag(rolling_weight) over (partition by identifier order by cntr desc) is NULL then rolling_weight 
     else rolling_weight - lag(rolling_weight) over (partition by identifier order by cntr desc)
end weight
from 
(
    select temp1.*,
     case when rolling_weight % 30 = 0 then 1 
          when rolling_weight = total_weight then 1 
          when weight + lead(weight) over (partition by identifier order by cntr desc) > 30 then 1
          else 0 end as flg
    from
    (
        select 
          cte.*, 
          sum(weight) over (partition by identifier order by item, cntr desc) rolling_weight,
          sum(weight) over (partition by identifier) total_weight
        from cte
    ) temp1
) temp2
where flg = 1
order by identifier, package