这样的需求如何实现

Question

create table tmp
as
select 2014 fphm,00000001 kshm from dual union all
select 2014 fphm,00000002 kshm from dual union all
select 2014 fphm,00000003 kshm from dual union all
select 2014 fphm,00000004 kshm from dual union all
select 2014 fphm,00000005 kshm from dual union all
select 2014 fphm,00000007 kshm from dual union all
select 2014 fphm,00000008 kshm from dual union all
select 2014 fphm,00000009 kshm from dual union all
select 2013 fphm,00000120 kshm from dual union all
select 2013 fphm,00000121 kshm from dual union all
select 2013 fphm,00000122 kshm from dual union all
select 2013 fphm,00000124 kshm from dual union all
select 2013 fphm,00000125 kshm from dual;实现效果如下:
2013,00000120,00000122 
2013,00000124,00000125 
2014,00000001,00000005 
2014,00000007,00000009 
   知道思路是使用lag和lead，希望大家指点下！

Answer 1

  以fphm分组,求kshm连续值的首尾。上帝保佑，我这样说，希望大家能明白！

Answer 2

SQL> with t1 as (select fphm,kshm,kshm+row_number()over(partition by fphm order by kshm desc) rn from tmp)
  2  select fphm,min(kshm),max(kshm) from t1 group by fphm,rn;
 
      FPHM  MIN(KSHM)  MAX(KSHM)
---------- ---------- ----------
      2013        120        122
      2013        124        125
      2014          1          5
      2014          7          9
 
SQL> 

Answer 3

SQL> with t1 as (select fphm,kshm,kshm+row_number()over(partition by fphm order by kshm desc) rn from tmp1)
  2  select fphm,min(kshm),max(kshm) from t1 group by fphm,rn;
 
      FPHM MIN(KSHM)                                                                        MAX(KSHM)
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
      2013 00000120                                                                         00000122
      2013 00000124                                                                         00000125
      2014 00000001                                                                         00000005
      2014 00000007                                                                         00000009
 
SQL> 

Answer 4

create table tmp
as
select 2014 fphm,00000001 kshm from dual union all
select 2014 fphm,00000002 kshm from dual union all
select 2014 fphm,00000003 kshm from dual union all
select 2014 fphm,00000004 kshm from dual union all
select 2014 fphm,00000005 kshm from dual union all
select 2014 fphm,00000007 kshm from dual union all
select 2014 fphm,00000008 kshm from dual union all
select 2014 fphm,00000009 kshm from dual union all
select 2013 fphm,00000120 kshm from dual union all
select 2013 fphm,00000121 kshm from dual union all
select 2013 fphm,00000122 kshm from dual union all
select 2013 fphm,00000124 kshm from dual union all
select 2013 fphm,00000125 kshm from dual;
select FPHM,min(kshm),max(kshm) from tmp
group by FPHM,kshm-rownum
order by fphm

Answer 5

--网上找的
--地址http://blog.csdn.net/wh62592855/archive/2010/03/15/5383249.aspx
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E,
min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from tmp)
where nvl(E-S-1,1)<>0

Answer 6

SELECT fphm,Min(kshm),Max(kshm) FROM(
 SELECT fphm,kshm,Row_Number()over(PARTITION BY fphm ORDER BY KSHM ) rn FROM tmp
)
GROUP BY fphm,kshm-rn
having count(1)>1;

Answer 7

这个直接使用rownum的话会有潜在问题,可能会统计出错误数据,不建议直接使用rownum

Answer 8

SQL> edi
已写入 file afiedt.buf  1  select fphm,min(kshm),max(kshm) from tmp
  2  group by fphm,kshm-rownum
  3* order by fphm
SQL> /      FPHM  MIN(KSHM)  MAX(KSHM)
---------- ---------- ----------
      2013        120        122
      2013        124        125
      2014          1          5
      2014          7          9

Answer 9

用rownum肯定不行！不信你打乱测试数据试试！！

Answer 10

我也觉得会有问题  除非order by 再包一层

Answer 11

   恩,rownum不行,9楼是可以的，那帖也是我现在在研究的！

Answer 12

谢谢大家问题已经解决！
分享下对该需求的总结：--方法1:使用lag
--思路:利用lag取得前一行的kshm,然后和本行的kshm想比,如果差值为1,说明这一行和上一行是连续的.
--由于首尾的特殊性,故而需要先用max和min来获得首尾点.  
SQL> select fphm, nvl(lag(kshm,1)over(partition by fphm order by next_kshm),min_kshm),nvl(next_kshm,max_kshm)
  2    from (select fphm,
  3                 kshm,
  4                 lag(kshm,1)over(partition by fphm order by kshm) next_kshm,
  5                 min(kshm) over(partition by fphm)  min_kshm,
  6                 max(kshm) over(partition by fphm)  max_kshm
  7            from tmp
  8          )
  9    where  nvl(kshm-next_kshm-1, 1)<>0;
 
      FPHM NVL(LAG(KSHM,1)OVER(PARTITIONB NVL(NEXT_KSHM,MAX_KSHM)
---------- ------------------------------ -----------------------
      2013 00000120                       00000122
      2013 00000124                       00000125
      2014 00000001                       00000005
      2014 00000007                       00000009--方法二:使用row_number()
--思路:连续的kshm的大小与row_number成反比.
SQL> select fphm,min(kshm),max(kshm) from
  2  (
  3  select fphm,kshm,kshm+row_number()over(partition by fphm order by kshm desc) rn from tmp
  4  )
  5  group by fphm,rn;
 
      FPHM MIN(KSHM) MAX(KSHM)
---------- --------- ---------
      2013 00000120  00000122
      2013 00000124  00000125
      2014 00000001  00000005
      2014 00000007  00000009
      
SQL> select fphm,min(kshm),max(kshm) from
  2  (
  3   select fphm,kshm,row_number()over(partition by fphm order by kshm ) rn from tmp
  4  )
  5  group by fphm,kshm-rn;
 
      FPHM MIN(KSHM) MAX(KSHM)
---------- --------- ---------
      2013 00000120  00000122
      2013 00000124  00000125
      2014 00000001  00000005
      2014 00000007  00000009      
            
--方法三:使用rownum
SQL> select fphm,min(kshm),max(kshm)
  2   from(select fphm,kshm
  3          from tmp
  4          order by fphm,kshm
  5       )
  6   group by fphm,kshm-rownum
  7  order by fphm;
 
      FPHM MIN(KSHM) MAX(KSHM)
---------- --------- ---------
      2013 00000120  00000122
      2013 00000124  00000125
      2014 00000001  00000005
      2014 00000007  00000009