有如下数据
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'
单号 商品编号 期初数量 销售数量 来货数量 结存数量
10 1 0 0 10 10
10 2 0 0 5 5
11 1 10 0 1 11
12 1 11 3 0 8
12 2 5 1 0 4
生成时间>='2015-01-02' and 生成为时间是<='2015-01-03'
单号 商品编号 期初数量 销售数量 来货数量 结存数量
11 1 10 0 1 11
12 1 11 3 0 8
12 2 5 1 0 4
------解决思路----------------------
SELECT t2.*,
t1.生成时间,
ROW_NUMBER() OVER(PARTITION BY t2.商品编号 ORDER BY t1.生成时间) rn
INTO #table3
FROM #table1 t1
JOIN #table2 t2
ON t1.单号 = t2.单号
--SELECT * FROM #TABLE3 ORDER BY 商品编号, rn
SELECT t.单号,
t.商品编号,
ISNULL(p.结存数量,0) 期初数量,
t.销售数量,
t.来货数量,
t.结存数量
FROM #table3 t
LEFT JOIN #table3 p
ON t.商品编号 = p.商品编号
AND t.rn = p.rn + 1
WHERE t.生成时间>='2015-01-02'
AND t.生成时间<='2015-01-03'
ORDER BY t.商品编号, t.rn
1号～3号
单号 商品编号 期初数量 销售数量 来货数量 结存数量
----------- ----------- ----------- ----------- ----------- -----------
10 1 0 0 10 10
11 1 10 0 1 11
12 1 11 3 0 8
10 2 0 0 5 5
12 2 5 1 0 4
2号～3号
单号 商品编号 期初数量 销售数量 来货数量 结存数量
----------- ----------- ----------- ----------- ----------- -----------
11 1 10 0 1 11
12 1 11 3 0 8
12 2 5 1 0 4
------解决思路----------------------
;WITH CTE AS(我感觉也不一定要全表查询，可以一部分根据日期SCAN，再一部分SEEK
SELECT T2.*,T1.生成时间
,ROW_NUMBER()OVER(PARTITION BY T2.商品编号 ORDER BY T1.生成时间)RN
FROM #table1 T1
JOIN #table2 T2 ON T1.单号=T2.单号
WHERE T1.生成时间>='2015-01-02' AND T1.生成时间<='2015-01-03'
)
SELECT T1.单号,T1.商品编号,COALESCE(T2.结存数量,T5.结存数量,0) AS[期初数量],T1.销售数量,T1.来货数量,T1.结存数量
FROM CTE T1
LEFT JOIN CTE T2 ON T1.商品编号=T2.商品编号 AND T1.RN=T2.RN+1
OUTER APPLY(
SELECT TOP 1 T4.结存数量 FROM #table1 T3 JOIN #table2 T4 ON T3.单号=T4.单号
WHERE T1.RN=1 AND T1.商品编号=T4.商品编号 AND T3.生成时间<T1.生成时间
ORDER BY T3.生成时间 DESC
)T5