表A(商品编号spbh,进出量jc,日期rq)2000001 4.00 2011-05-05
2000001 4.00 2011-05-08
2000001 9.00 2011-05-27
2000001 5.00 2011-05-03
2000005 1.00 2011-05-08
2000005 1.00 2011-05-27
2000005 4.00 2011-05-13
2000005 -1.00 2011-05-29
2000006 10.00 2011-05-19
2000006 24.00 2011-05-03
2000006 20.00 2011-05-16
2000006 13.00 2011-05-24
2000007 8.00 2011-05-20
2000007 4.00 2011-05-25
表B(商品编号spbh,库存kc,库存日期kcrq)2000001 8.00 2011-05-05
2000005 -3.00 2011-05-05
2000006 6.00 2011-05-05
A表记录了商品的进出数量和日期,正的为进,负的为出
B表记录了2011-05-05这一天商品的库存。
算出商品5月每一天的库存,然后和B表的数据做对比。B表中没有的商品要做特殊标记
2000001 4.00 2011-05-08
2000001 9.00 2011-05-27
2000001 5.00 2011-05-03
2000005 1.00 2011-05-08
2000005 1.00 2011-05-27
2000005 4.00 2011-05-13
2000005 -1.00 2011-05-29
2000006 10.00 2011-05-19
2000006 24.00 2011-05-03
2000006 20.00 2011-05-16
2000006 13.00 2011-05-24
2000007 8.00 2011-05-20
2000007 4.00 2011-05-25
表B(商品编号spbh,库存kc,库存日期kcrq)2000001 8.00 2011-05-05
2000005 -3.00 2011-05-05
2000006 6.00 2011-05-05
A表记录了商品的进出数量和日期,正的为进,负的为出
B表记录了2011-05-05这一天商品的库存。
算出商品5月每一天的库存,然后和B表的数据做对比。B表中没有的商品要做特殊标记
select * from A where spbh = '2000001' and rq = '2011-05-05'2. B表中记录选择
select * from B where spbh = '2000001' and rq = '2011-05-05'3.第三个问题没有说明白,看不懂啊,你要怎么对比?怎么标记?举个例子。
2000001 -1 2011-05-01 2011-05-05 8.00
2000001 -1 2011-05-02 2011-05-05 8.00
2000001 4 2011-05-03 2011-05-05 8.00
2000001 4 2011-05-04 2011-05-05 8.00
2000001 8 2011-05-05 2011-05-05 8.00
2000001 8 2011-05-06 2011-05-05 8.00
2000001 8 2011-05-07 2011-05-05 8.00
...
2000001 12 2011-05-08 2011-05-05 8.00
2000001 12 2011-05-09 2011-05-05 8.00
2000001 12 2011-05-10 2011-05-05 8.00
...
2000001 12 2011-05-26 2011-05-05 8.00
2000001 17 2011-05-27 2011-05-05 8.00
2000001 17 2011-05-28 2011-05-05 8.00
2000001 17 2011-05-29 2011-05-05 8.00
2000001 17 2011-05-30 2011-05-05 8.00
2000001 17 2011-05-31 2011-05-05 8.00
通过B表中5号这天的库存量(kc)和A表中的进出量(jc)求出1-31号每一天的库存然后连接B表做对比
--生成测试数据
declare @tba table(spbh varchar(30),jc int,rq smalldatetime)
insert into @tba
select '2000001','4','2011-05-05' union all
select '2000001','4','2011-05-08' union all
select '2000001','9','2011-05-27' union all
select '2000001','5','2011-05-03' union all
select '2000005','1','2011-05-08' union all
select '2000005','1','2011-05-27' union all
select '2000005','4','2011-05-13' union all
select '2000005','-1','2011-05-29' union all
select '2000006','10','2011-05-19' union all
select '2000006','24','2011-05-03' union all
select '2000006','20','2011-05-16' union all
select '2000006','13','2011-05-24' union all
select '2000007','8','2011-05-20' union all
select '2000007','4','2011-05-25'
declare @tbb table(spbh varchar(30),kc int,kcrq smalldatetime)
insert into @tbb
select '2000001','8','2011-05-05' union all
select '2000005','-3','2011-05-05' union all
select '2000006','6','2011-05-05'--将@tba中新增的数据插入@tbb
insert into @tbb
select spbh,0,'2011-05-05' from @tba where spbh not in(select spbh from @tbb)--结果表@tbc
declare @tbc table (spbh varchar(30),spkc int,sj smalldatetime)
declare @sj smalldatetime
set @sj='2011-5-1'
while @sj<='2011-5-31'
begin
--分2个情况分别计算,插入结果表
if @sj<='2011-5-5'
--日期早于5/5,用5/5当天库存数-之前的入库数
insert into @tbc select spbh,kc-isnull((select sum(jc) from @tba where spbh=b.spbh and rq<='2011-5-5' and rq>@sj),0),@sj from @tbb b
else
--日期晚于5/5,用5/5当天库存数+之后的入库数
insert into @tbc select spbh,kc+isnull((select sum(jc) from @tba where spbh=b.spbh and rq>'2011-5-5' and rq<=@sj),0),@sj from @tbb b
set @sj=@sj+1
end--输出结果表
select * from @tbc order by spbh,sj