| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
jopen
11年前发布

Oracle 合并连续日期时间段

表结构:
Oracle 合并连续日期时间段
表数据:
Oracle 合并连续日期时间段
查询结果:
Oracle 合并连续日期时间段
SQL代码:

with gzltrb as ( select t.xmbh,      t.ygbh,      t.bjje,     t.ssrq,      lead(ssrq, 1, ssrq) over(order by ssrq) next_ssrq,     lag(ssrq, 1, ssrq) over(order by ssrq) prev_ssrq,     lead(t.bjje) over(order by ssrq) next_bjje,     lag(t.bjje) over(order by ssrq) prev_bjje from temp t order by xmbh, ygbh, ssrq )     select x.xmbh, x.ygbh,  x.ssrq as ksrq,     lead(x.prev_ssrq ,1 ,x.ssrq) over(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 else t.bjje - t.next_bjje end) as diff,              (case when t.prev_bjje is null then 1 else t.bjje - t.prev_bjje end) as prev_diff              from gzltrb t order by t.xmbh, t.ygbh, t.ssrq       )g      where g.prev_diff <> 0         ) x
来自:http://my.oschina.net/u/238212/blog/175759

 本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1384305904586.html
Oracle SQL