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

需求数据如下：

怎样查询出以下数据格式？0004 ---00060009 ---00180021 ---0021

数据准备：

 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.csdn.net/travylee/article/details/7392436nn间断和孤岛问题的解决思路

Re: ziwen004天前 12:48

回复TravyLeen谢谢分享，我会好好研究一下