分配数量

[caijunling]

CREATE TABLE [dbo].[Stock](
[StockID] [int] IDENTITY(1,1) NOT NULL,
[PartName] [varchar](50) NULL,
[Stock] [int] NULL,
[DateValue] [datetime] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (1, N'A', 100, CAST(0x00009DB700000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (2, N'B', -50, CAST(0x00009DB700000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (3, N'A', 60, CAST(0x00009DBE00000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (4, N'B', 53, CAST(0x00009DBE00000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (5, N'B', 41, CAST(0x00009DC500000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (6, N'A', 99, CAST(0x00009DC500000000 AS DateTime))
需要对A，B分配数值为100的数量，分配后A，B原来的数量加上分配后的数量的和相互最接近。如果不能平分的化，余数放在B上面。
分配 结果
1 A 100 2010-7-18 0 100
2 B -50 2010-7-18 100 50
(B只有负50,所以100的数量全部给B,这样 -50+100 和A原来的数值 100最接近

3 A 60 2010-7-25 46 106
4 B 53 2010-7-25 54 107

5 B 41 2010-8-1 79 120
6 A 99 2010-8-1 21 120

[jaydom]

帮顶 。。。。

[王向飞]

有点意思的题目 先顶

[华夏小卒]

[playwarcraft]

A,B 一定是成對出現嗎？

select  stockID,partName, stock, datevalue, 
 adjust=  case when stock>=tmp_adjust then 0 
               else case when   tmp_adjust - stock  >=100 then 100 
                      else tmp_adjust-stock end
                end
from
(
  select  A.*,  total, case when A.stockID%2=1 then s_stock else l_stock end as tmp_adjust
  from  Stock A
  left join
  (
  select tmp=(stockID-1)/2, sum(stock)+100 as total,  ceiling((sum(stock)+100)/2.0) as s_stock , floor((sum(stock)+100)/2.0) as l_stock  from stock
  group by (stockID-1)/2
  ) B
  on (A.stockID-1)/2 =B.tmp
) X

[playwarcraft]

CREATE TABLE [dbo].[Stock]( 
[StockID] [int] IDENTITY(1,1) NOT NULL, 
[PartName] [varchar](50) NULL, 
[Stock] [int] NULL, 
[DateValue] [datetime] NULL 
) ON [PRIMARY] 
GO 

INSERT [dbo].[Stock] ( [PartName], [Stock], [DateValue]) VALUES ( N'A', 100, CAST(0x00009DB700000000 AS DateTime)) 
INSERT [dbo].[Stock] ( [PartName], [Stock], [DateValue]) VALUES ( N'B', -50, CAST(0x00009DB700000000 AS DateTime)) 
INSERT [dbo].[Stock] ([PartName], [Stock], [DateValue]) VALUES ( N'A', 60, CAST(0x00009DBE00000000 AS DateTime)) 
INSERT [dbo].[Stock] ( [PartName], [Stock], [DateValue]) VALUES ( N'B', 53, CAST(0x00009DBE00000000 AS DateTime)) 
INSERT [dbo].[Stock] ( [PartName], [Stock], [DateValue]) VALUES ( N'B', 41, CAST(0x00009DC500000000 AS DateTime)) 
INSERT [dbo].[Stock] ([PartName], [Stock], [DateValue]) VALUES ( N'A', 99, CAST(0x00009DC500000000 AS DateTime)) 
INSERT [dbo].[Stock] ( [PartName], [Stock], [DateValue]) VALUES ( N'B', 20, CAST(0x00009DC500000000 AS DateTime)) 
INSERT [dbo].[Stock] ([PartName], [Stock], [DateValue]) VALUES ( N'A', -50, CAST(0x00009DC500000000 AS DateTime)) 

select  StockID,PartName, Stock, datevalue, adjust as [調整數量],  Stock+adjust  as [調整后數量]
FROM
(
  select  stockID,partName, stock, datevalue, 
   adjust=  case when stock>=tmp_adjust then 0 
               else case when   tmp_adjust - stock  >=100 then 100 
                      else tmp_adjust-stock end
                end
  from
  (
    select  A.*,  total, case when A.stockID%2=1 then s_stock else l_stock end as tmp_adjust
    from  Stock A
    left join
    (
    select tmp=(stockID-1)/2, sum(stock)+100 as total,  ceiling((sum(stock)+100)/2.0) as s_stock , floor((sum(stock)+100)/2.0) as l_stock  from stock
    group by (stockID-1)/2
    ) B
    on (A.stockID-1)/2 =B.tmp
  ) X
) Y

/*
StockID     PartName                                           Stock       datevalue                                              調整數量                 調整后數量                 
----------- -------------------------------------------------- ----------- ------------------------------------------------------ -------------------- --------------------- 
1           A                                                  100         2010-07-18 00:00:00.000                                0                    100
2           B                                                  -50         2010-07-18 00:00:00.000                                100                  50
3           A                                                  60          2010-07-25 00:00:00.000                                47                   107
4           B                                                  53          2010-07-25 00:00:00.000                                53                   106
5           B                                                  41          2010-08-01 00:00:00.000                                79                   120
6           A                                                  99          2010-08-01 00:00:00.000                                21                   120
7           B                                                  20          2010-08-01 00:00:00.000                                15                   35
8           A                                                  -50         2010-08-01 00:00:00.000                                85                   35

*/
GO
drop table stock