Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to realize SQL query continuous number segment". In daily operation, I believe many people have doubts on how to realize SQL query continuous number segment. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts of "how to realize SQL query continuous number segment"! Next, please follow the small series to learn together!

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

(There may be continuous data in the second field, and there may be breakpoints.)

how can we find out such results, find out continuous records.

Like the one below?

2014,00000001,00000005

2014,00000009,00000007

2013,00000120,00000122

2013,00000124,00000125

Method 1: Quoted from hmxxyy.

The copy code is 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 2: Use the lag/lead analysis function to process.. The method upstairs is really good to use is that the table scan/table connection is more, the amount of data may be large. Speed will be slower, of course, my method due to the use of analysis function used more frequently. So the amount of sorting may be more than the previous one..

The copy code is 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

13 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,

10 lead(kshm,1,null) over (partition by fphm order by kshm) next_kshm,

11 lag(kshm,1,null) over (partition by fphm order by kshm) prev_kshm

12 from t

13 )

14 where ( next_kshm - kshm 1 or kshm - prev_kshm 1 )

15 or ( next_kshm is null or prev_kshm is null )

16 )

17 )

18 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

This morning, I found myself in the middle of the night. I posted it and shared it with everyone ^_^.

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

13 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 /

FPHM MIN(B.KSHM) MAX(B.KSHM)

---------- ----------- -----------

2013 120 122

2013 124 125

2014 1 5

2014 7 9

SQL>

At this point, the study on "how to achieve SQL query consecutive number segments" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.