SQL存储过程中算法问题，100分

Question

仓库出货，以上数据今天要出货的数据，货物会发往不同的店铺，如上海S1、北京S2、广州S1 ….. ，货物装箱，每箱20件。
相同店铺、款式的货物装在一起，属性不限，装箱数量有多的分配到下一箱，不够的从下一箱抽取缺数补齐，同时生成箱号。
按照上面的要求，上海S1店铺款式PO001(红色字体) 应该生成如下的数据格式。
要求在存储过程中实现，生成结果集。
这个算法问题，请大家帮忙看看。 原始数据序号 店铺  款式  属性 数量
1 上海-S1  PO1001  SKU1 32
2 上海-S1  PO1001  SKU1 40
3 上海-S2  PO1002  SKU1 56
4 上海-S2  PO1002  SKU2 6
5 上海-S3  PO1003  SKU1 45
6 北京-S2  PO1005  SKU2 28
7 北京-S2  PO1005  SKU3 54
8 北京-S1  PO1004  SKU1 33
9 北京-S1  PO1004  SKU2 35
10 广州-S1  PO1006  SKU1 63
11 广州-S2  PO1007  SKU2 47
结果数据
箱号 店铺 款式 属性 数量
1 上海-S1 PO1001 SKU1 20 32件多12件,分配到下一箱
2 上海-S1 PO1001 SKU1 12 剩余12件
2 上海-S1 PO1001 SKU1 8 从下一箱抽取8件，补齐20件,箱号为2
3 上海-S1 PO1001 SKU1 20 剩余32件，再抽20件为第3箱
4 上海-S1 PO1001 SKU1 12 剩余12件为第4箱

Answer 1

一般都采用递归,不知道楼主的SQL是不是2005及以上

Answer 2

LZ是否是知道有多少个整箱，有多少个剩余数呢？if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (序号 int,店铺 nvarchar(10),款式 nvarchar(12),属性 nvarchar(8),数量 int)
insert into [TB]
select 1,'上海-S1','PO1001','SKU1',32 union all
select 2,'上海-S1','PO1001','SKU1',40 union all
select 3,'上海-S2','PO1002','SKU1',56 union all
select 4,'上海-S2','PO1002','SKU2',6 union all
select 5,'上海-S3','PO1003','SKU1',45 union all
select 6,'北京-S2','PO1005','SKU2',28 union all
select 7,'北京-S2','PO1005','SKU3',54 union all
select 8,'北京-S1','PO1004','SKU1',33 union all
select 9,'北京-S1','PO1004','SKU2',35 union all
select 10,'广州-S1','PO1006','SKU1',63 union all
select 11,'广州-S2','PO1007','SKU2',47select * from [TB]DECLARE @i INT --箱子的标准装入数量
SET @i = 20SELECT 店铺,款式,SUM(数量) AS 总数,SUM(数量)/@i AS 整箱数,SUM(数量)%@i AS 剩余数
FROM dbo.TB
GROUP BY 店铺,款式
/*
店铺         款式           总数          整箱数         剩余数
---------- ------------ ----------- ----------- -----------
上海-S1      PO1001       72          3           12
上海-S2      PO1002       62          3           2
上海-S3      PO1003       45          2           5
北京-S1      PO1004       68          3           8
北京-S2      PO1005       82          4           2
广州-S1      PO1006       63          3           3
广州-S2      PO1007       47          2           7(7 行受影响)*/

Answer 3

这个,我觉得需要使用游标去慢慢算了.游标的基本写法declare @id int,@name varchar(20);
declare cur cursor fast_forward for
  select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
   --做你要做的事
   fetch next from cur into @id,@name;
end
close cur;
deallocate cur;

Answer 4

多谢，我一般是用while循环来做的，没有用游标，就是里面的判断比较复杂。

Answer 5

create table tb(序号 int,店铺 nvarchar(10),款式 varchar(10),属性 varchar(10),数量 int)
insert into tb select 1,'上海-S1','PO1001','SKU1',32
insert into tb select 2,'上海-S1','PO1001','SKU1',28
insert into tb select 3,'上海-S1','PO1001','SKU1',50
go
;with cte as(
select top 1 1 as 箱号,序号,店铺,款式,属性,
case when 数量>20 then 20 else 数量 end 数量,
case when 数量>20 then 数量-20 else 0 end 余量,
case when 数量>20 then 20 else 数量 end 已装
from tb order by 序号
union all
select 箱号+1,序号,店铺,款式,属性,
case when 余量>20 then 20 else 余量 end ,
case when 余量>20 then 余量-20 else 0 end,
case when 余量>20 then 20 else 余量 end
from cte where 余量>0
union all
select a.箱号,b.序号,b.店铺,b.款式,b.属性,
case when a.数量+b.数量>20 then 20-a.数量 else b.数量 end, 
case when a.数量+b.数量>20 then a.数量+b.数量-20 else 0 end,
case when a.数量+b.数量>20 then 20 else b.数量 end
from cte a inner join tb b on a.序号=b.序号-1 and a.已装<20 
union all
select a.箱号+1,b.序号,b.店铺,b.款式,b.属性,
case when b.数量>20 then 20 else b.数量 end ,
case when b.数量>20 then b.数量-20 else 0 end,
case when b.数量>20 then 20 else b.数量 end 已装
from cte a inner join tb b on a.序号=b.序号-1 and a.数量=20 and a.余量=0 and 已装=20
)
select 箱号,序号,店铺,款式,属性,数量 from cte order by 箱号
/*
箱号          序号          店铺         款式         属性         数量
----------- ----------- ---------- ---------- ---------- -----------
1           1           上海-S1      PO1001     SKU1       20
2           1           上海-S1      PO1001     SKU1       12
2           2           上海-S1      PO1001     SKU1       8
3           2           上海-S1      PO1001     SKU1       20
4           3           上海-S1      PO1001     SKU1       20
5           3           上海-S1      PO1001     SKU1       20
6           3           上海-S1      PO1001     SKU1       10(7 行受影响)*/
go
drop table tb

Answer 6

create table tb(序号 int,店铺 nvarchar(10),款式 varchar(10),属性 varchar(10),数量 int)
insert into tb select 1,'上海-S1','PO1001','SKU1',32
insert into tb select 2,'上海-S1','PO1001','SKU1',40
insert into tb select 3,'上海-S2','PO1002','SKU1',56
insert into tb select 4,'上海-S2','PO1002','SKU2',6
insert into tb select 5,'上海-S3','PO1003','SKU1',45
insert into tb select 6,'北京-S2','PO1005','SKU2',28
insert into tb select 7,'北京-S2','PO1005','SKU3',54
insert into tb select 8,'北京-S1','PO1004','SKU1',33
insert into tb select 9,'北京-S1','PO1004','SKU2',35
insert into tb select 10,'广州-S1','PO1006','SKU1',63
insert into tb select 11,'广州-S2','PO1007','SKU2',47
go
;with cte as(
select 1 as 箱号,序号,店铺,款式,属性,
case when 数量>20 then 20 else 数量 end 数量,
case when 数量>20 then 数量-20 else 0 end 余量,
case when 数量>20 then 20 else 数量 end 已装
from tb a where not exists(select 1 from tb where 店铺=a.店铺 and 序号<a.序号)
union all
select 箱号+1,序号,店铺,款式,属性,
case when 余量>20 then 20 else 余量 end ,
case when 余量>20 then 余量-20 else 0 end,
case when 余量>20 then 20 else 余量 end
from cte where 余量>0
union all
select a.箱号,b.序号,b.店铺,b.款式,b.属性,
case when a.数量+b.数量>20 then 20-a.数量 else b.数量 end, 
case when a.数量+b.数量>20 then a.数量+b.数量-20 else 0 end,
case when a.数量+b.数量>20 then 20 else b.数量 end
from cte a inner join tb b on a.序号=b.序号-1 and a.已装<20 and b.店铺=a.店铺
union all
select a.箱号+1,b.序号,b.店铺,b.款式,b.属性,
case when b.数量>20 then 20 else b.数量 end ,
case when b.数量>20 then b.数量-20 else 0 end,
case when b.数量>20 then 20 else b.数量 end 已装
from cte a inner join tb b on a.序号=b.序号-1 and a.数量=20 and a.余量=0 and 已装=20 and b.店铺=a.店铺
),cte2 as(
select max(箱号)x,min(序号)h,店铺 from cte group by 店铺
),cte3 as(
select 店铺,(select sum(x) from cte2 where h<=a.h)累计箱号,h from cte2 a
)
select a.箱号+b.累计箱号-(select min(累计箱号) from cte3)累计箱号,a.序号,a.店铺,a.款式,a.属性,a.数量
from cte a inner join cte3 b on a.店铺=b.店铺 order by a.序号,箱号
/*
累计箱号        序号          店铺         款式         属性         数量
----------- ----------- ---------- ---------- ---------- -----------
1           1           上海-S1      PO1001     SKU1       20
2           1           上海-S1      PO1001     SKU1       12
2           2           上海-S1      PO1001     SKU1       8
3           2           上海-S1      PO1001     SKU1       20
4           2           上海-S1      PO1001     SKU1       12
5           3           上海-S2      PO1002     SKU1       20
6           3           上海-S2      PO1002     SKU1       20
7           3           上海-S2      PO1002     SKU1       16
7           4           上海-S2      PO1002     SKU2       4
8           4           上海-S2      PO1002     SKU2       2
8           5           上海-S3      PO1003     SKU1       20
9           5           上海-S3      PO1003     SKU1       20
10          5           上海-S3      PO1003     SKU1       5
13          6           北京-S2      PO1005     SKU2       20
14          6           北京-S2      PO1005     SKU2       8
14          7           北京-S2      PO1005     SKU3       12
15          7           北京-S2      PO1005     SKU3       20
16          7           北京-S2      PO1005     SKU3       20
17          7           北京-S2      PO1005     SKU3       2
17          8           北京-S1      PO1004     SKU1       20
18          8           北京-S1      PO1004     SKU1       13
18          9           北京-S1      PO1004     SKU2       7
19          9           北京-S1      PO1004     SKU2       20
20          9           北京-S1      PO1004     SKU2       8
21          10          广州-S1      PO1006     SKU1       20
22          10          广州-S1      PO1006     SKU1       20
23          10          广州-S1      PO1006     SKU1       20
24          10          广州-S1      PO1006     SKU1       3
24          11          广州-S2      PO1007     SKU2       20
25          11          广州-S2      PO1007     SKU2       20
26          11          广州-S2      PO1007     SKU2       7(31 行受影响)*/
go
drop table tb

Answer 7

小弟不太会用游标。
并且这个存储过程是要在页面 Page_Load 的时候计算结果集，来绑定的，数据量应该不大，用游标也不会影响性能吧？
谁帮忙写个游标处理的呢？多谢拉。

Answer 8

看了这段SQL，不是太懂。在查询结果中好像有点问题哦 。
第8、17、24箱，都是两个不同的店铺和款式，应该是不能装在一起的。8           4           上海-S2      PO1002     SKU2       2
8           5           上海-S3      PO1003     SKU1       2017          7           北京-S2      PO1005     SKU3       2
17          8           北京-S1      PO1004     SKU1       2024          10          广州-S1      PO1006     SKU1       3
24          11          广州-S2      PO1007     SKU2       20

Answer 9

create table tb(序号 int,店铺 nvarchar(10),款式 varchar(10),属性 varchar(10),数量 int)
insert into tb select 1,'上海-S1','PO1001','SKU1',32
insert into tb select 2,'上海-S1','PO1001','SKU1',40
insert into tb select 3,'上海-S2','PO1002','SKU1',56
insert into tb select 4,'上海-S2','PO1002','SKU2',6
insert into tb select 5,'上海-S3','PO1003','SKU1',45
insert into tb select 6,'北京-S2','PO1005','SKU2',28
insert into tb select 7,'北京-S2','PO1005','SKU3',54
insert into tb select 8,'北京-S1','PO1004','SKU1',33
insert into tb select 9,'北京-S1','PO1004','SKU2',35
insert into tb select 10,'广州-S1','PO1006','SKU1',63
insert into tb select 11,'广州-S2','PO1007','SKU2',47
go
--递归处理
;with cte as(
--获取各店铺,款式的第一箱
select 序号 as flg,1 as 箱号,序号,店铺,款式,属性,
case when 数量>20 then 20 else 数量 end 数量,
case when 数量>20 then 数量-20 else 0 end 余量,
case when 数量>20 then 20 else 数量 end 已装
from tb a where not exists(select 1 from tb where 店铺=a.店铺 and 款式=a.款式 and 序号<a.序号)
union all
--对余量大于0的,进行再分箱(由前面或后面而来)
select flg,箱号+1,序号,店铺,款式,属性,
case when 余量>20 then 20 else 余量 end ,
case when 余量>20 then 余量-20 else 0 end,
case when 余量>20 then 20 else 余量 end
from cte where 余量>0
union all
--如箱未满,且有同店同款货,继续装入
select a.flg,a.箱号,b.序号,b.店铺,b.款式,b.属性,
case when a.数量+b.数量>20 then 20-a.数量 else b.数量 end, 
case when a.数量+b.数量>20 then a.数量+b.数量-20 else 0 end,
case when a.数量+b.数量>20 then 20 else b.数量 end
from cte a inner join tb b on a.序号=b.序号-1 and a.已装<20 and b.店铺=a.店铺 and a.款式=b.款式
union all
--如果箱已满,且正好将上一序号装完,但后一序号仍有同店同款货,则新开一箱
select a.flg,a.箱号+1,b.序号,b.店铺,b.款式,b.属性,
case when b.数量>20 then 20 else b.数量 end ,
case when b.数量>20 then b.数量-20 else 0 end,
case when b.数量>20 then 20 else b.数量 end 已装
from cte a inner join tb b on a.序号=b.序号-1 and a.数量=20 and a.余量=0 and 已装=20 and b.店铺=a.店铺 and a.款式=b.款式
),cte1 as(
--找出同店同款最大箱号
select flg,max(箱号)xh from cte group by flg
),cte2 as(
--对最大箱号进行统计
select flg,isnull((select sum(xh) from cte1 where flg<a.flg),0)xhadd from cte1 a
)
--把统计后的箱号按序号依次递增
select a.箱号+b.xhadd as 箱号,a.序号,a.店铺,a.款式,a.属性,a.数量 
from cte a inner join cte2 b on a.flg=b.flg
order by 序号,箱号--查询结果
/*
箱号          序号          店铺         款式         属性         数量
----------- ----------- ---------- ---------- ---------- -----------
1           1           上海-S1      PO1001     SKU1       20
2           1           上海-S1      PO1001     SKU1       12
2           2           上海-S1      PO1001     SKU1       8
3           2           上海-S1      PO1001     SKU1       20
4           2           上海-S1      PO1001     SKU1       12
5           3           上海-S2      PO1002     SKU1       20
6           3           上海-S2      PO1002     SKU1       20
7           3           上海-S2      PO1002     SKU1       16
7           4           上海-S2      PO1002     SKU2       4
8           4           上海-S2      PO1002     SKU2       2
9           5           上海-S3      PO1003     SKU1       20
10          5           上海-S3      PO1003     SKU1       20
11          5           上海-S3      PO1003     SKU1       5
12          6           北京-S2      PO1005     SKU2       20
13          6           北京-S2      PO1005     SKU2       8
13          7           北京-S2      PO1005     SKU3       12
14          7           北京-S2      PO1005     SKU3       20
15          7           北京-S2      PO1005     SKU3       20
16          7           北京-S2      PO1005     SKU3       2
17          8           北京-S1      PO1004     SKU1       20
18          8           北京-S1      PO1004     SKU1       13
18          9           北京-S1      PO1004     SKU2       7
19          9           北京-S1      PO1004     SKU2       20
20          9           北京-S1      PO1004     SKU2       8
21          10          广州-S1      PO1006     SKU1       20
22          10          广州-S1      PO1006     SKU1       20
23          10          广州-S1      PO1006     SKU1       20
24          10          广州-S1      PO1006     SKU1       3
25          11          广州-S2      PO1007     SKU2       20
26          11          广州-S2      PO1007     SKU2       20
27          11          广州-S2      PO1007     SKU2       7(31 行受影响)*/
go
drop table tb

Answer 10

另,本程序要求有一个连续的序号,并且相同店铺,款式集中在一起.如果现有序号不连续,或内容不连续,应用
select *,row_number()over(order by 店铺,款式) as rn from tb
新生成一个,再以此rn代替现有的序号进行分箱.

Answer 11

多谢"晴天"解决了我的问题，可以安心放国庆长假啦，在这里衷心祝"晴天"，也祝大家国庆Happy.

Answer 12

我也写了一个，测试过也可以;with myT1 as 
(
select *, 总数=(select SUM(数量) from #tb T2 where  T2.店铺=T1.店铺 and T2.款式=T1.款式 and T1.序号>T2.序号 ) from #tb T1 
), myT2 as 
( 
select T1.序号,T1.店铺,T1.款式,T1.属性,T1.数量,新数量=case when isnull(总数,0)%20=0 then T1.数量 else T1.数量-(20-总数%20) end,
承上数量=case when isnull(总数,0)%20=0 then 0 else 20-总数%20 end,箱数=(T1.数量-(case when isnull(总数,0)%20=0 then 0 else 20-总数%20 end))/20,余数=isnull((T1.数量+总数),T1.数量)%20 
from #tb T1 inner join myT1 T2 on T2.店铺=T1.店铺 and T2.款式=T1.款式 and T2.序号=T1.序号
),myT3 as 
(
select flag=1,序号,店铺,款式,属性,承上数量 数量 from myT2 
union all 
select flag+1,T1.序号,T1.店铺,T1.款式,T1.属性, 
case 
when flag>箱数 then 余数
else 20 
end from myT3 T1,myT2 T2 
where T2.店铺=T1.店铺 and T2.款式=T1.款式 and T2.序号=T1.序号 and flag<箱数+2
)
select * from myT3 where 数量<>0 order by 店铺,款式,序号,flag