我有一个表,要创建这样一个视图
表中有个字段A
字段A
0
1
2
3
4
5
我想得到的视图是
字段A 字段A
0 1
1 2
2 3
3 4
4 5
就是这个表的字段是两次出现在视图里,但是第二个字段A的值正好是第一个字段A的下一组值
请问如何实现,小弟在这里谢谢大家了
表中有个字段A
字段A
0
1
2
3
4
5
我想得到的视图是
字段A 字段A
0 1
1 2
2 3
3 4
4 5
就是这个表的字段是两次出现在视图里,但是第二个字段A的值正好是第一个字段A的下一组值
请问如何实现,小弟在这里谢谢大家了
select a,
lead(a)over(order by a) a1
from a
)where a1 is not null;
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 A1 A2
---------- ----------
0 1
1 2
2 3
3 4
4 5
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;
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;