按你的示例来看,分析函数lag可以达到你的要求: select Date,nvl(count1,1,null) count1,nvl(count2,1,null) count2 from ( select nvl(A.date,B.Date) Date,A.count1,B.count2 from A full join B on A.date=B.Date )
不好意思,漏写了。 select Date,nvl(count1,lag(count1,1,null)) count1,nvl(count2,lag(count2,1,null)) count2 from ( select nvl(A.date,B.Date) Date,A.count1,B.count2 from A full join B on A.date=B.Date )
抱歉,还是漏了。select Date1,nvl(count1,lag(count1,1,null) over (order by date1)) count1,nvl(count2,lag(count2,1,null) over ( order by date1)) count2 from ( select nvl(A.date,B.Date) Date1,A.count1,B.count2 from A full join B on A.date=B.Date order by Date1 )
from A full join B on A.date=B.Date如果上面的两个"Date"字段的数据类型是Date的,最好先用to_char转换一下。
date count1 count2
2005-10-01 2 null
2005-10-02 3 4
2005-10-03 5 6
2005-10-05 5(*) 4
*如果2005-10-05为空,则等于上一日数据,如何得到?
因为我要得到的count是每日叠加的
select Date,nvl(count1,1,null) count1,nvl(count2,1,null) count2
from
(
select nvl(A.date,B.Date) Date,A.count1,B.count2
from A full join B on A.date=B.Date
)
select Date,nvl(count1,lag(count1,1,null)) count1,nvl(count2,lag(count2,1,null)) count2
from
(
select nvl(A.date,B.Date) Date,A.count1,B.count2
from A full join B on A.date=B.Date
)
from
(
select nvl(A.date,B.Date) Date1,A.count1,B.count2
from A full join B on A.date=B.Date
order by Date1
)
我2005-10-01吃了2苹果,
2005-10-01吃了1苹果,一共吃了3个
03吃了2 , 5
04没有吃 ,一共吃了应该还是5个,这个5怎么得到这种说法与 “如果2005-10-05为空,则等于上一日数据”的说法应该是两个意思的吧。
是不是要分别用SQL用实现,我前面实现的是后一种说法。
日期(上面的date,string型) 吃苹果数(新增) 吃总数(上面的count1)
2005-10-01 2 2
2005-10-02 1 3
2005-10-03 2 5
2005-10-05 0(当天没有吃) 5 (但总数应该还是5)下班了,回家再讨论
如何得到表C
date count1 count2
2005-10-01 2 null
2005-10-02 3 4
2005-10-03 5 6
2005-10-05 5(*) 4
*如果2005-10-05为空,则等于上一日数据,如何得到?
因为我要得到的count是每日叠加的
nvl(A.count1,0), [sumcount1] --如果当天没有吃苹果,count1为0,sumcount1为上一日值,如何取?
nvl(B.count2,0), [sumcount2] --当天没有花钱,总共花钱是上一日的值。如何取?
from A full join B on A.date=B.Date--sumcount1,sumcount2已经是叠加后的结果了。表:A:吃苹果
date count1 sumcount1
2005-10-01 2 2
2005-10-02 1 3
2005-10-03 2 5表B: 花钱
date count2 sumcount2
2005-10-02 4 4
2005-10-03 2 6
2005-10-05 4 8用我的sql结果为:
date count1 sumcount1 count2 sumcount2
2005-10-01 2 2 0 null
2005-10-02 1 3 4 4
2005-10-03 2 5 2 6
2005-10-05 0 null 4 8count1,count2问题已经解决,如果为null,取0
关键问题,sumcount1,sumcount2 如果为null,取上一条记录,如果是第一条记录,取0????