表结构如下
KEY1 VARHCAR2(6)
KEY2 VARCHAR2(6)
数据上KEY2的值全部是补0满6位的数值，但是存在不连续的，例如
003001
003003
003004
004011
004013
现在要通过SQL实现用户输入003我就要找到最小的空号“003002”，用户输入00401我就要找到“004012”
已经纠结2天，请问有什么好方法。谢谢
------解决方案--------------------
WITH T AS
(SELECT '003001' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003004' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005011' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL)
SELECT LPAD(MIN(T1.C1) + 1, 6, 0)
FROM (SELECT C1,
LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP
FROM T
WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('00401', 1, 3)) T1
WHERE T1.GAP > 1
OR T1.GAP IS NULL;
------解决方案--------------------
WITH T AS
(SELECT '003001' C1
FROM DUAL
UNION ALL
SELECT '003003' C1
FROM DUAL
UNION ALL
SELECT '003007' C1
FROM DUAL
UNION ALL
SELECT '004011' C1
FROM DUAL
UNION
SELECT '005001' C1
FROM DUAL
UNION ALL
SELECT '004013' C1
FROM DUAL
UNION
SELECT '020002' C1
FROM DUAL
UNION ALL
SELECT '020003' C1
FROM DUAL)
SELECT LPAD(DECODE(T2.GAP,
NULL,
(SELECT DECODE(SUBSTR(MIN(T3.C1), -3),
'001',
MIN(T3.C1) + 1,
MIN(T3.C1) - 1)
FROM T T3
WHERE SUBSTR(T3.C1, 1, 3) = SUBSTR('020', 1, 3)),
T2.C1 + 1),
6,
0)
FROM (SELECT T1.C1, T1.GAP
FROM (SELECT C1,
LEAD(TO_NUMBER(C1)) OVER(ORDER BY TO_NUMBER(C1)) - TO_NUMBER(C1) GAP
FROM T
WHERE SUBSTR(T.C1, 1, 3) = SUBSTR('020', 1, 3)) T1
WHERE T1.GAP > 1
OR T1.GAP IS NULL