问题:
假设有一个表,一列int字段存放数字,值如: 1 2 3 4 5 8 9 10 20 21 22
其中1 2 3 4 5是连续的,8 9 10是连续的,20 21 22是连续的,请问如何用sql找出每个连续序列中的第一个数字,也就是找出1、8、20三个数字。
连续是指最少两个数字是相邻的,就可以看作为一个连续序列。
总的数据量不大,几万条到十几万条不等。这个操作是对已经收集到的数据做分析过滤用的,所以性能不用考虑太多。
初始化:
db2 create table test30 (col1 int)
以下为每种方法和该方法使用的SQL语句的执行计划
方法1:
select col1
from test30 tt
where tt.col1+1 in (select t.col1 from test30 t )
and tt.col1-1 not in (select t.col1 from test30 t )
![1.JPG](http://www.db2china.net/club/attachments/month_1010/1010111920d510879b3eb49c51.jpg)
方法2:
select col2+1 from (
select col2 from (select col1-1 col2 from test30)
except select col1 col2 from test30
)
![2.JPG](http://www.db2china.net/club/attachments/month_1010/101011192036eb70c8bb7b9f6e.jpg)
方法3:
with t ( n1,n2) as
(
select col1, lag(col1,1) OVER(ORDER BY col1) AS col2
from test30
)
select n1 from t
where (n2 is null ) or ( n1- n2 > 1)
![3.JPG](http://www.db2china.net/club/attachments/month_1010/10101119207353a3ba89924d07.jpg)
方法4:
with tmp (num) as (
SELECT col1 FROM test30
),
tmp2 (num, next_num) as (
select num, coalesce(min(num) over(order by num rows between 1 following and 1 following), num+1) next_num
from tmp)
select num
from (
select num, next_num-min(next_num) over(order by next_num rows between 1 preceding and 1 preceding) gap
from tmp2
where next_num-num = 1) a
where coalesce(gap,2) >1
![4.JPG](http://www.db2china.net/club/attachments/month_1010/10101119208a5ca635ce05f110.jpg)