跪求鞋业订单装箱配码算法

Question

现在客户有这么一个要求（用存储过程实现）：订单有445对鞋，
--34码---35码---36码--37码--38码
--45对--150对--150对--50对--50对
先我要求用8对装的箱子装，系统自动算出要几个外箱及每箱的装箱明细（而且每箱不能只装一个码数，除非是尾数）。
请高手给个思路。谢谢！！

Answer 1

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-03 12:00:00
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT)
INSERT [tb]
SELECT 34,45 UNION ALL
SELECT 35,150 UNION ALL
SELECT 36,150 UNION ALL
SELECT 37,50 UNION ALL
SELECT 38,50
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT ISNULL(LTRIM(A),'合计') 尺码,SUM(CEILING(B*1./8)) AS 外箱数
FROM TB
GROUP BY A
WITH ROLLUP
/*
尺码           外箱数
------------ ---------------------------------------
34           6
35           19
36           19
37           7
38           7
合计           58(6 行受影响)
*/

Answer 2

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-03 12:00:00
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT)
INSERT [tb]
SELECT 34,45 UNION ALL
SELECT 35,150 UNION ALL
SELECT 36,150 UNION ALL
SELECT 37,50 UNION ALL
SELECT 38,50
GO
--SELECT * FROM [tb]-->SQL查询如下:
IF NOT OBJECT_ID('[sp_getqty]') IS NULL
DROP PROC [sp_getqty]
GO
CREATE PROC [sp_getqty]
@a int=NULL, --起始尺码,为NULL时表时所有尺码
@b int=NULL  --终止尺码,为NULL时表时所有尺码 
AS
SELECT ISNULL(LTRIM(A),'合计') 尺码,SUM(CEILING(B*1./8)) AS 外箱数
FROM TB
WHERE a BETWEEN ISNULL(@a,a) AND  ISNULL(@b,a)
GROUP BY A
WITH ROLLUP
GO--调用：
EXEC [sp_getqty]
/*
尺码           外箱数
------------ ---------------------------------------
34           6
35           19
36           select 18*8
37           7
38           7
合计           58(6 行受影响)
*/EXEC [sp_getqty] 34,36
/*
尺码           外箱数
------------ ---------------------------------------
34           6
35           19
36           19
合计           44(4 行受影响)*/

Answer 3

 (不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 4

TONY哥,你这样是没考虑合并其它的鞋子在一起,445/8<=56个就行了,其它的可以装一起,也可以不用装一起

Answer 5

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-03 12:00:00
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([a] INT,[b] INT)
INSERT [tb]
SELECT 34,45 UNION ALL
SELECT 35,150 UNION ALL
SELECT 36,150 UNION ALL
SELECT 37,50 UNION ALL
SELECT 38,50
GO
--SELECT * FROM [tb]-->SQL查询如下:
IF NOT OBJECT_ID('[sp_getqty]') IS NULL
DROP PROC [sp_getqty]
GO
CREATE PROC [sp_getqty]
@A INT=NULL, --起始尺码,为NULL时表时所有尺码
@B INT=NULL  --终止尺码,为NULL时表时所有尺码 
AS
WITH T AS
(
SELECT RN=(ROW_NUMBER()OVER(ORDER BY A.B%8 DESC)-1)/8,
外箱='混装'+LTRIM((ROW_NUMBER()OVER(ORDER BY A.A)-1)/8+1),A.A 尺码
FROM TB A 
JOIN MASTER..SPT_VALUES B
ON B.TYPE='P' AND B.NUMBER BETWEEN 1 AND A.B%8
WHERE A.A BETWEEN ISNULL(@A,A) AND ISNULL(@B,B)
),
T1 AS
(
SELECT  DISTINCT PX=1,外箱 ,外箱数量=1,
尺码=STUFF((SELECT DISTINCT ','+LTRIM(尺码) FROM T WHERE 外箱=A.外箱 FOR XML PATH('')),1,1,'')
FROM T A
UNION ALL
SELECT PX=0,外箱='外箱'+LTRIM(ROW_NUMBER()OVER(ORDER BY A)),
B/8 AS 外箱数,LTRIM(A) 尺码
FROM TB 
WHERE A BETWEEN ISNULL(@A,A) AND ISNULL(@B,B)
UNION ALL
SELECT PX=3,'合计',CEILING(SUM(B*1./8)),'' FROM TB WHERE A BETWEEN ISNULL(@A,A) AND ISNULL(@B,B)
)
SELECT 外箱,外箱数量,尺码 FROM T1 ORDER BY PX,外箱
GO--调用：
EXEC [sp_getqty]
/*
外箱                           外箱数量                                    尺码
---------------------------- --------------------------------------- ------------------------------------
外箱1                          5                                       34
外箱2                          18                                      35
外箱3                          18                                      36
外箱4                          6                                       37
外箱5                          6                                       38
混装1                          1                                       34,35
混装2                          1                                       35,36
混装3                          1                                       36,37,38
合计                           56                                      (9 行受影响)
*/EXEC [sp_getqty] 34,36
/*
---------------------------- --------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
外箱1                          5                                       34
外箱2                          18                                      35
外箱3                          18                                      36
混装1                          1                                       34,35
混装2                          1                                       35,36
混装3                          1                                       36
合计                           44                                      (7 行受影响)
*/

Answer 6

补上图片：http://hi.baidu.com/%C1%F5%D6%C7%BD%DC/album/item/44cfba31a6e0fc9e5fdf0ec6.html

Answer 7

tony ，你可能还不了解这个问题。我来帮xiao163liu详细说明下需求。这个问题我也考虑过。呵呵，代码写不出来。
我们就拿鞋来举例，先介绍下鞋的特点。鞋有分款号，颜色，配码。同款同色的鞋按照配码装箱，配码的码段有分男女。一般男码从39-44，女码从36-41。不能按照配码装箱的是散码。
 数据： 鞋的数量就按照刘兄弟的数据。码段34-38，一件为8双。
 要求： 根据指定配码计算每个配码的箱数，不能按照配码装箱的散码按码号显示，支持再根据配码装箱。直到尾数<8

Answer 8

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-03 19:20:29
--  Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
--          Mar 29 2009 10:27:29 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------IF NOT OBJECT_ID('[订单信息表]') IS NULL
    DROP TABLE [订单信息表]
GO
CREATE TABLE [订单信息表](ID int identity primary key,订单号 nvarchar(10),类型 nvarchar(10),[尺码] INT,[订单量] INT)
INSERT [订单信息表]
SELECT 'SE001','女鞋',34,8 UNION ALL
SELECT 'SE001','女鞋',35,10 UNION ALL
SELECT 'SE001','女鞋',36,19 UNION ALL
SELECT 'SE001','女鞋',37,17 UNION ALL
SELECT 'SE001','女鞋',38,16 UNION ALL
SELECT 'SE001','女鞋',39,8 UNION ALL
SELECT 'SE001','女鞋',40,8 UNION ALL
SELECT 'SE002','童鞋',27,10 UNION ALL
SELECT 'SE002','童鞋',28,13 UNION ALL
SELECT 'SE002','童鞋',29,19 UNION ALL
SELECT 'SE002','童鞋',30,19 UNION ALL
SELECT 'SE002','童鞋',31,18 UNION ALL
SELECT 'SE002','童鞋',32,11 UNION ALL
SELECT 'SE002','童鞋',33,11
GO
--SELECT sum([订单量]) FROM [订单信息表] where 类型='童鞋' --101IF NOT OBJECT_ID('[装箱配比表]') IS NULL
    DROP TABLE [装箱配比表]
GO
CREATE TABLE [装箱配比表](ID INT IDENTITY,类型 VARCHAR(20),尺码 int,单箱数量 int)
INSERT [装箱配比表] 
SELECT '女鞋',34,1 UNION ALL
SELECT '女鞋',35,1 UNION ALL
SELECT '女鞋',36,2 UNION ALL
SELECT '女鞋',37,2 UNION ALL
SELECT '女鞋',38,2 UNION ALL
SELECT '女鞋',39,1 UNION ALL
SELECT '女鞋',40,1 UNION ALL
SELECT '童鞋',27,2 UNION ALL
SELECT '童鞋',28,2 UNION ALL
SELECT '童鞋',29,3 UNION ALL
SELECT '童鞋',30,3 UNION ALL
SELECT '童鞋',31,3 UNION ALL
SELECT '童鞋',32,2 UNION ALL
SELECT '童鞋',33,2   
GOIF OBJECT_ID('SP_GETQTY') IS NOT NULL
DROP PROC SP_GETQTY
GO
CREATE PROC SP_GETQTY
@TYPE NVARCHAR(20)='女鞋'
AS
DECLARE @S NVARCHAR(MAX)
SELECT @S=ISNULL(@S+',','')+QUOTENAME(尺码) 
FROM [装箱配比表]
WHERE 类型=@TYPE
EXEC(
'WITH T AS
(
SELECT A.*,B.单箱数量,
CEILING(SUM(A.订单量)OVER(PARTITION BY A.订单号,A.类型)*1./
SUM(B.单箱数量)OVER(PARTITION BY A.订单号,A.类型)) AS 理论箱数
FROM [订单信息表] A
JOIN [装箱配比表] B
ON A.类型=B.类型 AND A.尺码=B.尺码
WHERE B.类型='''+@TYPE+'''
)
,T1 AS
(
SELECT A.*,B.NUMBER+1 箱号
FROM T A
JOIN MASTER..SPT_VALUES B
ON B.TYPE=''P'' AND B.NUMBER<A.理论箱数 
)
,T2 AS
(
SELECT 订单号,类型,尺码,箱号,
CASE WHEN 箱号=理论箱数 THEN 订单量-单箱数量*箱号+单箱数量
 WHEN 箱号* 单箱数量<=订单量 THEN 单箱数量
 ELSE 0
END AS 实际装箱量
FROM T1 
) 
SELECT * 
FROM T2 A 
PIVOT(MAX(实际装箱量) FOR 尺码 IN('+@S+')) B'
)
GOEXEC SP_GETQTY '女鞋'
/*
订单号        类型         箱号          34          35          36          37          38          39          40
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
SE001      女鞋         1           1           1           2           2           2           1           1
SE001      女鞋         2           1           1           2           2           2           1           1
SE001      女鞋         3           1           1           2           2           2           1           1
SE001      女鞋         4           1           1           2           2           2           1           1
SE001      女鞋         5           1           1           2           2           2           1           1
SE001      女鞋         6           1           1           2           2           2           1           1
SE001      女鞋         7           1           1           2           2           2           1           1
SE001      女鞋         8           1           1           2           2           2           1           1
SE001      女鞋         9           0           2           3           1           0           0           0(9 行受影响)
*/EXEC SP_GETQTY '童鞋'
/*
订单号        类型         箱号          27          28          29          30          31          32          33
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
SE002      童鞋         1           2           2           3           3           3           2           2
SE002      童鞋         2           2           2           3           3           3           2           2
SE002      童鞋         3           2           2           3           3           3           2           2
SE002      童鞋         4           2           2           3           3           3           2           2
SE002      童鞋         5           2           2           3           3           3           2           2
SE002      童鞋         6           0           3           4           4           3           1           1(6 行受影响)
*/此题相当麻烦,花了一夜的时间.只能计算到在最后一箱调整零数.