有如下数据
create table #table1 (单号 int,生成时间 datetime)
create table #table2 (单号 int ,商品编号 int ,销售数量 int,来货数量 int,结存数量 int)
insert into #table1(单号,生成时间)
values (10,'2015-01-01'),(11,'2015-01-02'),(12,'2015-01-03')
insert into #table2(单号,商品编号,销售数量,来货数量,结存数量)
values(10,1,0,10,10)
,(10,2,0,5,5)
,(11,1,0,1,11)
,(12,1,3,0,8)
,(12,2,1,0,4)
现需一个语句,按查询的时间段,得到相应的期初数量,各项汇总业务数量,期未数量,但期初数量不能通过 加减得到,必须是获取上一次的结存数量,还需考虚效率,这张表里的数据量会很大,如下
生成时间>='2015-01-01' and 生成为时间是<='2015-01-03'
商品编号 期初数量 销售数量 来货数量 结存数量
1 0 3 11 8
2 0 1 5 4
生成时间>='2015-01-02' and 生成为时间是<='2015-01-03'
商品编号 期初数量 销售数量 来货数量 结存数量
1 10 3 1 8
2 5 1 0 4
------解决思路----------------------
DECLARE @StartDate DATETIME,@EndDate DATETIME
SELECT @StartDate='2015-01-01',@EndDate='2015-01-03'
;WITH CTE AS(
SELECT T2.商品编号
,SUM(T2.销售数量)OVER(PARTITION BY T2.商品编号)[销售数量]
,SUM(T2.来货数量)OVER(PARTITION BY T2.商品编号)[来货数量]
,T2.结存数量
,ROW_NUMBER()OVER(PARTITION BY T2.商品编号 ORDER BY T1.生成时间 DESC)RN
FROM #table1 T1
JOIN #table2 T2 ON T1.单号=T2.单号
WHERE T1.生成时间>=@StartDate AND T1.生成时间<=@EndDate
)
SELECT T1.商品编号,ISNULL(T5.结存数量,0) AS[期初数量],T1.销售数量,T1.来货数量,T1.结存数量
FROM CTE T1
OUTER APPLY(
SELECT TOP 1 T4.结存数量 FROM #table1 T3 JOIN #table2 T4 ON T3.单号=T4.单号
WHERE T1.RN=1 AND T1.商品编号=T4.商品编号 AND T3.生成时间<@StartDate
ORDER BY T3.生成时间 DESC
)T5
WHERE T1.RN=1