试试吧,没经过测试,只是想想看,可以这么写。注:需要8i支持。
select stcdt,lag(stcdt,1) over(order by ymdhm) as stcdt_1,lag(stcdt,2) over(order by ymdhm) as stcdt_2,
,max(sum(DYRN) over (order by YMDHM rows 3 preceding)) as result
from 表
group by stcdt,lag(stcdt,1) over(order by ymdhm) as stcdt_1,lag(stcdt,2) over(order by ymdhm) as stcdt_2
再换一个,我的sql经测试,有问题。我又换了一种写法,只求效果,不求效率的。
前三列就是结果。试试吧:
select b.stcdt,b.stcdt_1,b.stcdt_2,b.result from (
select stcdt,
lag(stcdt,1) over(order by ymdhm) as stcdt_1,
lag(stcdt,2) over(order by ymdhm) as stcdt_2,
sum(DYRN) over (order by YMDHM rows between 1 preceding and 1 following) as result
from 表) b
where b.result= (
select max(a.result) from (
select stcdt,
lag(stcdt,1) over(order by ymdhm) as stcdt_1,
lag(stcdt,2) over(order by ymdhm) as stcdt_2,
sum(DYRN) over (order by YMDHM rows between 1 preceding and 1 following) as result
from 表) a)
/