An ingenious solution to SQL query continuous number segment _ Database other

Yesterday in Itpub saw this post, the question feels interesting, thought carefully. A solution is also given.:-)

Ask for help, please the Master of guidance.

I have a table structure,
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

(The second field may be contiguous data, and a breakpoint may exist.) )

How to query out such a result, query a continuous record.

Like the following?

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

Method One: Reference from HMXXYY.

Copy Code code as follows:

Sql> select * from Gap;

ID SEQ
---------- ----------
1 1
1 4
1 5
1 8
2 1
2 2
2 9

Select Res1.id, Res2.seq str, RES1.SEQ end
From (
Select RowNum RN, c.*
From (
SELECT *
From Gap A
Where NOT EXISTS (
Select null from Gap b where b.id = a.id and a.seq = b.seq-1
)
Order by ID, seq
) c
) Res1, (
Select RowNum RN, d.*
From (
SELECT *
From Gap A
Where NOT EXISTS (
Select null from Gap b where b.id = a.id and a.seq = b.seq + 1
)
Order by ID, seq
) d
) Res2
where res1.id = Res2.id
and Res1.rn = Res2.rn
/

ID STR End
--------- ---------- ----------
1 1 1
1 4 5
1 8 8
2 1 2
2 9 9

Method Two: Use Lag/lead analysis function to handle. The way upstairs is really useful is to think that table scan/table connection More, may be large data. It's going to be slow, of course. This approach is more frequent because of the use of analytic functions. So the number of sorts may be more than the previous one.

Copy Code code as follows:

Sql> Select Fphm,lpad (kshm,8, ' 0 ') kshm
2 from T
3/

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

Rows selected.

Sql> set echo on
sql> @bbb. sql
Sql> Select Fphm,lpad (kshm,8, ' 0 ') start_kshm,lpad (prev_prev_kshm,8, ' 0 ') end_kshm
2 from (
3 Select Fphm,kshm,next_kshm,prev_kshm,
4 Lag (kshm,1,null) over (partition by FPHM ORDER by Kshm) Next_next_kshm,
5 lead (Kshm,1,null) over (partition by FPHM ORDER by KSHM) Prev_prev_kshm
6 from (
7 SELECT *
8 from (
9 Select Fphm,kshm,
The lead (kshm,1,null) over (partition by the FPHM order by Kshm) Next_kshm,
One lag (kshm,1,null) over (partition by FPHM ORDER by KSHM) Prev_kshm
From T
13)
Where (Next_kshm-kshm <> 1 or kshm-prev_kshm <> 1)
or (NEXT_KSHM is null or PREV_KSHM is null)
16)
17)
where Next_kshm-kshm = 1
19/

FPHM Start_kshm End_kshm
---------- ---------------- ----------------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

Sql> Spool Off

Method Three: This morning wildflower gave me this answer, suddenly feel refreshing ah. Just post it and share it with all the ^_^.

Sql> Spool Aaa.log
Sql> set echo on
Sql> select * from T;

No rows selected

Sql> select * from T;

FPHM KSHM
---------- ----------
2014 1
2014 2
2014 3
2014 4
2014 5
2014 7
2014 8
2014 9
2013 120
2013 121
2013 122

FPHM KSHM
---------- ----------
2013 124
2013 125

Rows selected.

Sql> @bbb. SQL
Sql> Select B.fphm,min (B.KSHM), Max (B.KSHM)
  2  from (
  3           Select A.*,to_number (a.kshm-rownum) cc
  4           from (
  5                   SELECT * FROM T-order by FPHM,KSHM
  6           a
  7 )   B
  8  GROUP by b.fphm,b.cc
  9& nbsp /

FPHM MIN (B.KSHM) MAX (B.KSHM)
---------- ----------- -----------
2013 120 122
2013 124 125
2014 1 5
2014 7 9

Sql>