试试吧,没经过测试,只是想想看,可以这么写。注:需要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 id from aa;ID -- 1 2 3 4 5 6 67 rows selectedSQL> select id,sum(id) over(order by id rows 2 preceding) num from aa;ID NUM -- ---------- 1 1 2 3 3 6 4 9 5 12 6 15 6 177 rows selected统计记录本行以及前两行总和。 等等,正测试找出三条记录.
SQL> select lag(id,2,null) over(order by id) id2,lag(id,1,null) over(order by id) id1,id,sum(id) over(order by id rows 2 preceding) num from aa;ID2 ID1 ID NUM --- --- -- ---------- 1 1 1 2 3 1 2 3 6 2 3 4 9 3 4 5 12 4 5 6 15 5 6 6 17以上可以得到并行的三条和最大的id号
再换一个,我的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) /
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
--
1
2
3
4
5
6
67 rows selectedSQL> select id,sum(id) over(order by id rows 2 preceding) num from aa;ID NUM
-- ----------
1 1
2 3
3 6
4 9
5 12
6 15
6 177 rows selected统计记录本行以及前两行总和。
等等,正测试找出三条记录.
--- --- -- ----------
1 1
1 2 3
1 2 3 6
2 3 4 9
3 4 5 12
4 5 6 15
5 6 6 17以上可以得到并行的三条和最大的id号
前三列就是结果。试试吧:
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)
/