分配数量

Question

本帖最后由 caijunling 于 2010-07-15 14:08:02 编辑

Answer 1

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

Answer 2

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