17,089
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE HR.TEMP2
(
A NUMBER
);
insert into temp2 values(0);
insert into temp2 values(1);
insert into temp2 values(2);
insert into temp2 values(3);
insert into temp2 values(4);
insert into temp2 values(5);
select a1,a2 from (
select a a1,lag(a,1,-99999999999) over (order by a desc) a2
from temp2
)
where a2 <> -99999999999
order by a1
-- 或者视图可以这样定义:
create view t_v
as
select a1.a as a1, a2.a as a2
from t a1 inner join t a2 on a1.a=a2.a-1;
create table t(a NUMBER(18,0));
insert into t(a) values(0);
insert into t(a) values(1);
insert into t(a) values(2);
insert into t(a) values(3);
insert into t(a) values(4);
insert into t(a) values(5);
commit;
create view t_v
as
select a1.a as a1, a2.a as a2
from t a1 left join t a2 on a1.a=a2.a-1
where a2.a is not null;
select * from t_v;