鞋业订单装箱配码算法

--------------------------------------------------------------------------

--  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 类型='童鞋' --101

 

IF 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  

GO

 

IF 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'

)

GO

 

EXEC 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 行受影响)
*/