我有两张表
open表open_date open_number
2009-11-8 9
2009-11-9 10
2009-11-12 13
2009-11-13 12
cancel表
cancel_date cancel_number
2009-11-11 1
2009-11-13 2我想得到的结果是sum_date open_number cancel_number number
2009-11-8 9 0 9
2009-11-9 19 0 19
2009-11-11 10 1 9
2009-11-12 23 1 22
2009-11-13 35 3 32
2009-11-14
即open表与cancel表的日期合并
条件1:open_number和cancel_number随着日期递增条件2:合并后如果open表中没有的日期 ,其open_number为该日期前的最近一次open_number的数据
如 2009-11-11 所对应的open_number的数据为2009-11-9 的open_number 的数据19条件3:所得的cancel_number条件与条件2一致条件4:number为open_number - cancel_number我是用LEFT OUTER JOIN来连接,
我遇到的问题是当open表没有该日期,我如何获取该日期前的最近一次open_number的数据呢?
请各位给我一点思路吧,谢谢大家了
open表open_date open_number
2009-11-8 9
2009-11-9 10
2009-11-12 13
2009-11-13 12
cancel表
cancel_date cancel_number
2009-11-11 1
2009-11-13 2我想得到的结果是sum_date open_number cancel_number number
2009-11-8 9 0 9
2009-11-9 19 0 19
2009-11-11 10 1 9
2009-11-12 23 1 22
2009-11-13 35 3 32
2009-11-14
即open表与cancel表的日期合并
条件1:open_number和cancel_number随着日期递增条件2:合并后如果open表中没有的日期 ,其open_number为该日期前的最近一次open_number的数据
如 2009-11-11 所对应的open_number的数据为2009-11-9 的open_number 的数据19条件3:所得的cancel_number条件与条件2一致条件4:number为open_number - cancel_number我是用LEFT OUTER JOIN来连接,
我遇到的问题是当open表没有该日期,我如何获取该日期前的最近一次open_number的数据呢?
请各位给我一点思路吧,谢谢大家了
sum_date open_number cancel_number number
2009-11-8 9 0 9
2009-11-9 19 0 19
2009-11-11 10 1 9 --看你前面两条数据的open_number好像是本条数据的open_number加上最近一次的
2009-11-12 23 1 22 --最近一次应该为13+0。也就是13.为什么是25呢?
2009-11-13 35 3 32
2009-11-14
总觉得你结果得到的规律不一致。
--> 测试数据:@open
declare @open table([open_date] varchar(10),[open_number] int)
insert @open
select '2009-11-08',9 union all
select '2009-11-09',10 union all
select '2009-11-12',13 union all
select '2009-11-13',12
--> 测试数据:@cancel
declare @cancel table([cancel_date] varchar(10),[cancel_number] int)
insert @cancel
select '2009-11-11',1 union all
select '2009-11-13',2select * ,open_number - cancel_number as number
from
(select t.sum_date ,
isnull((select sum(open_number) from @open where open_date <= t.sum_date ),0) as open_number,
isnull((select sum(cancel_number) from @cancel where cancel_date <= t.sum_date ),0) as cancel_number
from
(select open_date as sum_date from @open
union
select cancel_date as sum_date from @cancel) t ) h
--结果
-----------------------------------
2009-11-08 9 0 9
2009-11-09 19 0 19
2009-11-11 19 1 18
2009-11-12 32 1 31
2009-11-13 44 3 41