lag函数使用遇到的问题
--测试环境 oracle 11g r2;window7系统--测试用例
create or replace view v(proj_id, proj_start, proj_end) as
select 1, date '2005-01-01', date '2005-01-02' from dual union all
select 2, date '2005-01-02', date '2005-01-03' from dual union all
select 3, date '2005-01-03', date '2005-01-04' from dual union all
select 4, date '2005-01-04', date '2005-01-05' from dual union all
select 5, date '2005-01-06', date '2005-01-07' from dual union all
select 6, date '2005-01-16', date '2005-01-17' from dual union all
select 7, date '2005-01-17', date '2005-01-18' from dual union all
select 8, date '2005-01-18', date '2005-01-19' from dual union all
select 9, date '2005-01-19', date '2005-01-20' from dual union all
select 10, date '2005-01-21', date '2005-01-22' from dual union all
select 11, date '2005-01-26', date '2005-01-27' from dual union all
select 12, date '2005-01-27', date '2005-01-28' from dual union all
select 13, date '2005-01-28', date '2005-01-29' from dual union all
select 14, date '2005-01-29', date '2005-01-30' from dual;
--查询连续的数据，连续是指 proj_start连续
--查询1
select a.proj_id,
a.proj_end,
lag(a.proj_end) over(order by 1) last_proj_end,
a.proj_start,
(case when a.proj_start = lag(a.proj_end) over(order by 1) then 1 else 0 end) status
from v a；
列 last_proj_end 为空
--查询2
select proj_id,
proj_end,
last_proj_end,
proj_start
from (select a.proj_id,
a.proj_end,
lag(a.proj_end) over(order by 1) last_proj_end,
a.proj_start,
(case when a.proj_start = lag(a.proj_end) over(order by 1) then 1 else 0 end) status
from v a);
列 last_proj_end 有数据
问题：查询1和查询2本质上是一致的，但是列 last_proj_end的数据会有差别？
页:
[1]