有一个表,存放某时间某商品ID的入库数量
内容如下
ID,quantity,createdate
01 5 2012-01-01
01 15 2012-01-03
01 6 2012-01-08
01 100 2012-05-04
02 3 2012-02-02
02 54 2012-05-06
……现在要出库,要求按时间先后顺序来出库,比如01这个商品要出23个(5+15+3),那么就要求把数据库变成
ID,quantity,createdate
01 0 2012-01-01
01 0 2012-01-03
01 3 2012-01-08
01 100 2012-05-04
02 3 2012-02-02
02 54 2012-05-06
……
有没有什么sql语句,又简单又高效?
内容如下
ID,quantity,createdate
01 5 2012-01-01
01 15 2012-01-03
01 6 2012-01-08
01 100 2012-05-04
02 3 2012-02-02
02 54 2012-05-06
……现在要出库,要求按时间先后顺序来出库,比如01这个商品要出23个(5+15+3),那么就要求把数据库变成
ID,quantity,createdate
01 0 2012-01-01
01 0 2012-01-03
01 3 2012-01-08
01 100 2012-05-04
02 3 2012-02-02
02 54 2012-05-06
……
有没有什么sql语句,又简单又高效?
set @ct=23
;with tb(ID,quantity,createdate)
as(
select '01',5,'2012-01-01' union all
select '01',15,'2012-01-03' union all
select '01',6,'2012-01-08' union all
select '01',100,'2012-05-04' union all
select '02',3,'2012-02-02' union all
select '02',54,'2012-05-06'
),tb1 as(
select *,ct=@ct,row=row_number()over(order by createdate) from tb where id='01'
),
cte as(
select ID,quantity=(case when ct<=0 then quantity when ct>=quantity then 0 else quantity-ct end),createdate,ct=ct-quantity,row from tb1 where row=1
union all
select t.id,quantity=(case when c.ct<=0 then t.quantity when c.ct>=t.quantity then 0 else t.quantity-c.ct end),t.createdate,c.ct-t.quantity,t.row from tb1 t join cte c on c.row+1=t.row
)
select ID,quantity,createdate from cte
union all
select * from tb where id!='01'
就你的例子,tb是商品表,那我有一个tb2,表示出库的商品数量内容如下:
ID,quantity
01 23
02 9
该如何一下子出库,使得tb的表变成
ID,quantity,createdate
01 0 2012-01-01
01 0 2012-01-03
01 3 2012-01-08
01 100 2012-05-04
02 0 2012-02-02
02 48 2012-05-06
as(
select '01',5,'2012-01-01' union all
select '01',15,'2012-01-03' union all
select '01',6,'2012-01-08' union all
select '01',100,'2012-05-04' union all
select '02',3,'2012-02-02' union all
select '02',54,'2012-05-06'
),
tb2(ID,quantity)
as(
select '01',23 union all
select '02',9
),
tb1 as(
select a.*,ct=b.quantity,row=row_number()over(partition by a.id order by createdate) from tb a left join tb2 b on a.id=b.id
),
cte as(
select ID,quantity=(case when ct-quantity>=0 then 0 else quantity-ct end),createdate,ct=ct-quantity,row from tb1 where row=1
union all
select t.id,quantity=(case when c.ct-t.quantity>=0 then 0 else t.quantity-c.ct end),t.createdate,c.ct-t.quantity,t.row from tb1 t join cte c on c.row+1=t.row and c.id=t.id
)
select ID,quantity,createdate from cte order by id,createdate