Server:高级更新查询
Server:高级更新查询
提问于 2017-03-21 16:03:56
我需要一些关于sql更新查询的帮助，该查询可以计算估计的每日库存消耗，
一开始我在桌子上放满了日用品。
declare @stock TABLE ([StockDate] [datetime] NULL,
[stock] [int])
insert into @stock
values ('2017/21/03', -3), ('2017/22/03', -1),
('2017/23/03', -5), ('2017/24/03', 0),
('2017/25/03', -4), ('2017/26/03', -7),
('2017/27/03', 0);- 初始库存: 12
- 订购数量: 10
- 订购点:3
- 21/03/2017股票= 12-3 =9
- 2017年3月22日股票= 9-1 =8
- 对于23/03/2017股票= 8-5 =3 <= OrderPoint (3)，则股票= 8-5 + 10 = 13。
我试过：
update s
set s.stock = case
when s.StockDate = '2017/21/03'
then 12
else s2.stock
end + s.stock
from @stock s
left join @stock s2 on s2.StockDate = DATEADD(day, -1, s.StockDate)实际结果：
Date Stock
2017-03-21 00:00:00.000 9
2017-03-22 00:00:00.000 -4
2017-03-23 00:00:00.000 -6
2017-03-24 00:00:00.000 -5
2017-03-25 00:00:00.000 -4
2017-03-26 00:00:00.000 -11
2017-03-27 00:00:00.000 -7问题，即日期-1还没有计算，并给出了错误的结果，该查询。
预期成果：
Date Stock
2017-03-21 00:00:00.000 9
2017-03-22 00:00:00.000 8
2017-03-23 00:00:00.000 13
2017-03-24 00:00:00.000 13
2017-03-25 00:00:00.000 9
2017-03-26 00:00:00.000 12
2017-03-27 00:00:00.000 12先谢谢你
回答 1
Stack Overflow用户
发布于 2017-03-21 16:34:43
在提供了获取“订单点”的方法之后，将sum() over()用于运行的总计：
declare @orderpoint table ([StockDate] [datetime] NULL, [stock] [int]);
insert into @orderpoint values
('20170323',10)
,('20170326',10);
select *
, RunningTotal = sum(stock) over (order by stockdate)
from (
select s.stockdate, stock = s.stock+isnull(o.stock,'')
from @stock s
left join @orderpoint o
on s.stockdate = o.stockdate
) t
order by stockdaterextester 演示：http://rextester.com/GHHU68033
返回：
+------------+-------+--------------+
| stockdate | stock | RunningTotal |
+------------+-------+--------------+
| 2017-03-20 | 12 | 12 |
| 2017-03-21 | -3 | 9 |
| 2017-03-22 | -1 | 8 |
| 2017-03-23 | 5 | 13 |
| 2017-03-24 | 0 | 13 |
| 2017-03-25 | -4 | 9 |
| 2017-03-26 | 3 | 12 |
| 2017-03-27 | 0 | 12 |
+------------+-------+--------------+对于update，您可以使用一个公共表表达式
;with cte as (
select
stockdate = convert(varchar(10),stockdate,120)
, stock
, RunningTotal = sum(stock) over (order by stockdate)
from (
select s.stockdate, stock = s.stock+isnull(o.stock,'')
from @stock s
left join @orderpoint o
on s.stockdate = o.stockdate
) t
)
update s
set stock = cte.stock
from @stock s
inner join cte
on s.stockdate = cte.stockdate;
select
stockdate = convert(varchar(10),stockdate,120)
, stock
from @stock;返回：
+------------+-------+
| stockdate | stock |
+------------+-------+
| 2017-03-20 | 12 |
| 2017-03-21 | 9 |
| 2017-03-22 | 8 |
| 2017-03-23 | 13 |
| 2017-03-24 | 13 |
| 2017-03-25 | 9 |
| 2017-03-26 | 12 |
| 2017-03-27 | 12 |
+------------+-------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42932352
复制相关文章
相似问题
腾讯云开发者
