延续的数字取中断的编写思路，希望有更好的方式

连续的数字取中断的编写思路，希望有更好的方式

需求数据如下：

怎样查询出以下数据格式？

0004 ---0006

0009 ---0018

0021 ---0021

数据准备：

CREATE TABLE foo( num VARCHAR2(10));

INSERT INTO foo VALUES('0001');
INSERT INTO foo VALUES('0002');
INSERT INTO foo VALUES('0003');
INSERT INTO foo VALUES('0007');
INSERT INTO foo VALUES('0008');
INSERT INTO foo VALUES('0019');
INSERT INTO foo VALUES('0020');
INSERT INTO foo VALUES('0022');

这个是一个典型的取中断的需求,求其中的中断开始到结束:

思路:

1)构造出补中断的开始数字,形成的数字列应该是:4,9,21

2)构造出补中断的结束数字,形成的数字列应该是:6,18,21

3)通过两个列的ROWNUM进行关联,得到4-6,9-18,21-21

过程:

1)如果上一个数字不存在于本列中,则符合条件,得到的结果如下:0004、0009、0021、0023

 SELECT LPAD(TO_NUMBER(A.NUM)+1, LENGTH(A.NUM), 0) AS NUM,
    RANK() OVER(ORDER BY A.NUM) AS NG
  FROM FOO A
 WHERE NOT EXISTS
 (SELECT 1 FROM FOO B WHERE TO_NUMBER(A.NUM) + 1 = TO_NUMBER(B.NUM))

2)如果下一个数字不存在于本列中,则符合条件,得到的结果如下:0000、0006、0018、0021

 SELECT LPAD(TO_NUMBER(A.NUM)-1, LENGTH(A.NUM), 0) AS NUM,
    RANK() OVER(ORDER BY A.NUM) AS NG
  FROM FOO A
 WHERE NOT EXISTS
 (SELECT 1 FROM FOO B WHERE TO_NUMBER(A.NUM) - 1 = TO_NUMBER(B.NUM))

3)上面使用RANK()分析函数得到了ROWNUM，通过ROWNUM关联得到结果，形成最终的SQL

 SELECT O.NUM,P.NUM FROM
(
SELECT LPAD(TO_NUMBER(A.NUM)+1, LENGTH(A.NUM), 0) AS NUM,
    RANK() OVER(ORDER BY A.NUM) AS NG
  FROM FOO A
 WHERE NOT EXISTS
 (SELECT 1 FROM FOO B WHERE TO_NUMBER(A.NUM) + 1 = TO_NUMBER(B.NUM))
 ) O,
( SELECT LPAD(TO_NUMBER(A.NUM)-1, LENGTH(A.NUM), 0) AS NUM,
    RANK() OVER(ORDER BY A.NUM) AS NG
  FROM FOO A
 WHERE NOT EXISTS
 (SELECT 1 FROM FOO B WHERE TO_NUMBER(A.NUM) - 1 = TO_NUMBER(B.NUM))
 )P
 WHERE O.NG+1 = P.NG

得到的结果:

0004 0006

0009 0018

0021 0021

这种方式符合用户的要求，但是有没有更好的方式呢？希望能够抛砖引玉......

1楼TravyLee4天前 12:36: http://blog.****.net/travylee/article/details/7392436nn间断和孤岛问题的解决思路

延续的数字取中断的编写思路，希望有更好的方式

相关推荐