解决方案 »
- rac增加新节点运行dbca 出现报错
- 如何在oracle report中调用外部的java及c++程序呢
- oracle分区与虚拟数据库实现信息系统数据集中 疑问
- 存储过程执行成功了,但在oracle中去调用的时候老是报错
- 怎么修改oracle9i里的默认时间格式?
- 请教一个导入oracle9i数据库的问题。
- 如何直接将select出来的多条记录放入RECORD多维数组里?
- 如何登陆到oracle manager server?具体的讲讲。
- 有谁装过oracle9i?帮我一下吧!!!
- oracle ORA-01033
- SQL*Loader-500:
- 请问如何在SQL中使用多条件的XPath表达式
id varchar2(10),
field1 number(4,1),
d_date date
)
insert into test_table values('001001',0.3,to_date('2011-7-28','yyyy-MM-dd'));
insert into test_table values('001002',0.4,to_date('2011-7-20','yyyy-MM-dd'));
insert into test_table values('002001',0.5,to_date('2011-7-21','yyyy-MM-dd'));
insert into test_table values('002002',0.6,to_date('2011-7-24','yyyy-MM-dd'));
insert into test_table values('003001',0.1,to_date('2011-7-23','yyyy-MM-dd'));
insert into test_table values('003002',0.6,to_date('2011-7-29','yyyy-MM-dd'));
insert into test_table values('003005',0.8,to_date('2011-7-21','yyyy-MM-dd'));
insert into test_table values('003004',0.9,to_date('2011-7-22','yyyy-MM-dd'));---------------------------------------------------------------------sql
select id,
sum(field1) as sum,
ltrim(max(sys_connect_by_path(d_date, ' ')), ' ') as value
from (select id,
field1,
d_date,
rnfirst,
lead(rnfirst) over(partition by id order by rnfirst) rnnext
from (select substr(t.id,0,3) id,
t.field1,
to_char(d_date,'yyyy-MM-dd') d_date,
row_number() over(order by t.id,t.d_date desc) rnfirst
from test_table t) temtable1) temptable2
start with rnnext is null
connect by rnnext = prior rnfirst
group by id
order by id
--------------------------------------------------------------------------------
试试吧,如果可以给个分哦!
sum(field1) over(partition by substr(id, 1, 3) order by d_date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING),
d_date,
lead(d_date) over(partition by substr(id, 1, 3) order by d_date) AS NEXT_DATE
from test_table) WHERE NEXT_DATE IS NOT NULL;