需求数据如下:
怎样查询出以下数据格式?
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
这种方式符合用户的要求,但是有没有更好的方式呢?希望能够抛砖引玉......