通过一个SQL获取号码所在区间的例子
最近的工作中有这样一个需求,根据一个字段的连续程度,获取其开始期间和终止期间
举例,
某个表t,结构如下:
SQL> desc t
Name
Type Nullable Default Comments
------ ----------- -------- ------- --------
USERID VARCHAR2(3) Y
编号
PERIOD VARCHAR2(6) Y
期间,格式为YYYYMM
|
t中的数据如下:
USERID PERIOD
------ ------
1 200701
1 200702
1 200703
1 200705
1 200706
2 200712
2 200708
7 rows selected
|
要实现的效果
USERID 开始期间
终止期间
------
------ ---------------------
1
200701 200703
1
200705 200706
2
200712 200712
2
200708 200708
|
使用的SQL语句,
SQL>select
b.USERID, min(b.PERIOD)
开始年月
, max(b.PERIOD)
终止年月
from (select a.*, to_number(a.PERIOD - rownum) c from (select * from t order by USERID, PERIOD) a) b group by b.USERID, b.c;
执行结果
USERID
开始年月
终止年月
------ -------- --------
1 200701 200703
编号
1
的,开始年月到终止年月
1 200705 200706
编号
1
的,有间断
2 200708 200708
编号
2
的,开始年月到终止年月
2 200712 200712
编号
2
的,有间断
|
这段代码原理其实很简单,主要就是利用Oracle的一个伪列rownum。利用年月与rownum的差作为标杆。我们改写一下语句,就可以看出来原委了。
SQL>select
rownum,b.USERID, b.PERIOD, b.c
from (select a.*, to_number(a.PERIOD - rownum) c from (select * from t order by USERID, PERIOD) a) b;
执行结果
ROWNUM
编号
年月
标杆
---------- ------ ------ ----------
1 1 200701 200700
2 1 200702 200700
3 1 200703 200700
4 1 200705 200701
5 1 200706 200701
6 2 200708 200702
7 2 200712 200705
7 rows selected
|
在上面这个例子中,当数据是数值时,显示是正确的,但是当数据是年月时,就会出现将200712和200801分开的情况,而二者应该是连续的。我们可以通过修改标杆来实现这种需求。
SQL>select
b.USERID, min(PERIOD),max(PERIOD),b.c
from (select a.*, months_between(to_date(a.PERIOD, 'YYYYMM' ),add_months(to_date( '299912' , 'YYYYMM' ),rownum)) c from (select * from t order by USERID, PERIOD) a) b group by b.USERID, b.c;
查询结果:
编号 开始年月 终止年月
标杆
------ ----------- ----------- ----------
1
200701 200703 -11916
1
200705 200706 -11915
2
200712 200801 -11910
|
months_between(to_date(a.PERIOD,
'YYYYMM'
),add_months(to_date(
'299912'
,
'YYYYMM'
)
,这个计算,得出标杆的数值即可。
299912
是一个足够大的值,可以根据自己的情况设置。
Wonder
2007-7-10