# 分组查询连接号段

fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

SQL> create table t(fphm varchar2(4),kshm varchar2(8));

SQL> insert into t values('2014','00000001');

SQL> insert into t values('2014','00000002');

SQL> insert into t values('2014','00000003');

SQL> insert into t values('2014','00000004');

SQL> insert into t values('2014','00000005');

SQL> insert into t values('2014','00000007');

SQL> insert into t values('2014','00000008');

SQL> insert into t values('2014','00000009');

SQL> insert into t values('2013','00000120');

SQL> insert into t values('2013','00000121');

SQL> insert into t values('2013','00000122');

SQL> insert into t values('2013','00000124');

SQL> insert into t values('2013','00000125');

SQL> commit;

SQL> select * from t;
FPHM KSHM
---- --------
2014 00000001
2014 00000002
2014 00000003
2014 00000004
2014 00000005
2014 00000007
2014 00000008
2014 00000009
2013 00000120
2013 00000121
2013 00000122

FPHM KSHM
---- --------
2013 00000124
2013 00000125

SQL> edit

1  select b.fphm,min(b.kshm),max(b.kshm) from
2  (select a.*,to_number(kshm-rownum) cc from (select * from t order by fphm,kshm) a)b
3* group by b.fphm,b.cc
SQL> /

FPHM MIN(B.KS MAX(B.KS
---- -------- --------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

SQL> edit

1  select a.fphm,min(kshm),max(kshm) from
2  (select t.*,t.kshm-row_number() over(partition by fphm order by kshm) rn from t) a
3* group by a.fphm,a.rn
SQL> /

FPHM MIN(KSHM MAX(KSHM
---- -------- --------
2014 00000007 00000009
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005

SQL> select max(fphm),min(kshm),max(kshm)
2  from t
3  group by fphm||kshm-rownum;

MAX( MIN(KSHM MAX(KSHM
---- -------- --------
2013 00000120 00000122
2014 00000001 00000005
2014 00000007 00000009
2013 00000124 00000125

• 博文量
106
• 访问量
662440