表结构: 表数据: 查询结果: SQL代码:
<!-- lang: sql -->
with temp as ( select t.xmbh,
t.ygbh,
t.bjje,
t.ssrq,
lead(ssrq, 1, ssrq) over(partition by xmbh order by ssrq) next_ssrq,
lag(ssrq, 1, ssrq) over(partition by xmbh order by ssrq) prev_ssrq,
lead(t.bjje) over(partition by xmbh order by ssrq) next_bjje,
lag(t.bjje) over(partition by xmbh order by ssrq) prev_bjje
from ( select 1 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-1','yyyy-mm-dd') as ssrq,10 as bjje from dual
union
select 2 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-2','yyyy-mm-dd') as ssrq,50 as bjje from dual
union
select 3 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-3','yyyy-mm-dd') as ssrq,100 as bjje from dual
union
select 4 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-4','yyyy-mm-dd') as ssrq,100 as bjje from dual
union
select 5 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-5','yyyy-mm-dd') as ssrq,100 as bjje from dual
union
select 6 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-6','yyyy-mm-dd') as ssrq,80 as bjje from dual
union
select 7 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-7','yyyy-mm-dd') as ssrq,80 as bjje from dual
union
select 8 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-8','yyyy-mm-dd') as ssrq,80 as bjje from dual
union
select 9 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-9','yyyy-mm-dd') as ssrq,100 as bjje from dual
union
select 10 as resource_id,'TEMP1' as xmbh, '201311120001' as ygbh,to_date('2013-8-10','yyyy-mm-dd') as ssrq,50 as bjje from dual
union
select 11 as resource_id,'TEMP2' as xmbh, '201311120001' as ygbh,to_date('2013-11-6','yyyy-mm-dd') as ssrq,200 as bjje from dual
union
select 12 as resource_id,'TEMP2' as xmbh, '201311120001' as ygbh,to_date('2013-11-7','yyyy-mm-dd') as ssrq,100 as bjje from dual ) t order by xmbh, ygbh, ssrq )
select * from (
select x.xmbh,
x.ygbh,
x.ssrq as ksrq,
lead(x.prev_ssrq ,1 ,x.ssrq) over(partition by x.xmbh order by x.ssrq) jsrq,
x.bjje
from ( select g.xmbh,
g.ygbh,
g.bjje,
g.ssrq,
(case when g.diff <> 0 then g.ssrq else g.next_ssrq end) as next_ssrq,
g.prev_ssrq,
g.diff,
g.prev_diff
from (select t.*,
(case when t.next_bjje is null then 1 when t.next_ssrq - t.ssrq > 1 then 1 else t.bjje - t.next_bjje end) as diff,
(case when t.prev_bjje is null then 1 when t.ssrq - t.prev_ssrq > 1 then 1 else t.bjje - t.prev_bjje end) as prev_diff
from temp t order by t.xmbh, t.ygbh, t.ssrq )g
union (select xmbh,
ygbh,
-999 as bjje,
max(ssrq) + 1 as ssrq,
max(ssrq) + 1 as next_ssrq,
max(ssrq) as prev_ssrq,
1 as diff,
1 as pre_diff
from temp group by xmbh, ygbh)
) x where x.prev_diff <> 0 ) where bjje <> -999