通过一个SQL获取号码所在区间的例子

 

通过一个SQL获取号码所在区间的例子

 

最近的工作中有这样一个需求，根据一个字段的连续程度，获取其开始期间和终止期间

举例，

某个表t，结构如下：

 

SQL> desc t Name   Type        Nullable Default Comments ------ ----------- -------- ------- -------- USERID VARCHAR2(3) Y           编号 PERIOD VARCHAR2(6) Y           期间，格式为YYYYMM

 

t中的数据如下：

 

SQL> select * from 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