--语法
--向后 LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
--向前 LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
select A,lead(A,1,A)over(order by px asc)LEAD_DATA,lag(A,1,A)over(order by px asc)LAG_DATE,B,C,D,PX,type from
(
select 1 A,2 B,3 C,4 D,1 px,'s' type from dual
union all
select 12 A,20 B,3 C,12 D,3 px,'s' type from dual
union all
select 5 A,6 B,7 C,8 D,2 px,'m' type from dual
union all
select 5 A,6 B,7 C,8 D,4 px,'m' type from dual
)
--在些基础上可做灵活扩展处理[分组排序]
select A,lead(A,1,A)over(partition by type order by px asc)LEAD_DATA,lag(A,1,A)over(partition by type order by px asc)LAG_DATE,B,C,D,PX,type from
(
select 1 A,2 B,3 C,4 D,1 px,'s' type from dual
union all
select 12 A,20 B,3 C,12 D,3 px,'s' type from dual
union all
select 5 A,6 B,7 C,8 D,2 px,'m' type from dual
union all
select