曾经用过使用rownum与排好顺序的字段做对比来比较是否存在缺失的键值。
不过你这个问题,因为不是从1开始大排序的可能有点难度。
不过如果想使用sql,应该从rownum找找思路。。
不过你这个问题,因为不是从1开始大排序的可能有点难度。
不过如果想使用sql,应该从rownum找找思路。。
解决方案 »
- oracle在insert时如何根据count进行插入控制
- Oracle PL/SQL一个小程序,输出多个员工的信息?
- 关于oracle内存数据库timesten的求教!!!
- 如何判断远程数据库的类型啊!
- redhat 5.7 下安装 11G 后白屏。急求帮助
- 数据库数据表的内部结构是什么呀,是不是一个结构体数组呀?
- 大家能否帮忙列一下锁表的几种典型原因
- 请各位高手帮忙!
- 请问大数据量插入(或者 merge into),提升速度的方法有哪些?
- 大佬们看看这个是什么问题
- 哪位大神帮忙写个function???
- ORACLE调用webservice时报badRequest : 401 unauthorized错误,求高手指点
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 '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;谢谢回答,但是这个算法只保证了增加方向的取得,要求往前也能取。
例如: 020002 020003的情况下,输入02可以返回020001
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
ORDER BY T1.C1) T2
WHERE ROWNUM = 1;
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 ALL
SELECT '004012' C1 FROM DUAL
UNION ALL
SELECT '004013' C1 FROM DUAL
UNION ALL
SELECT '004111' C1 FROM DUAL
UNION ALL
SELECT '004019' C1 FROM DUAL
UNION ALL
SELECT '005011' C1 FROM DUAL)SELECT
CASE
WHEN T_CNT = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0')
WHEN F_FLG = 0 THEN &tp||LPAD('1', 6 - LENGTH(&tp), '0')
ELSE R_MIN
END AS R_MIN
FROM (
SELECT COUNT(*) AS T_CNT
, NVL(SUM(DECODE(RE, NULL, 0, 1)), 0) AS R_CNT
, MIN(RE) AS R_MIN
, SUM(DECODE(SUBSTR(C1, LENGTH(&tp) + 1, 6 - LENGTH(&tp)), 1, 1, 0)) AS F_FLG
FROM(
SELECT TA.*,
CASE
WHEN AC IS NOT NULL THEN NULL
ELSE LPAD(C1 + 1, 6, '0')
END AS RE
FROM (
SELECT ROW_NUMBER()OVER(ORDER BY T1.C1) AS RID, T1.C1, T2.C1 AS AC
FROM DATASET T1
LEFT JOIN DATASET T2 ON T1.C1 = T2.C1 - 1
WHERE SUBSTR(T1.C1, 0, LENGTH(&tp)) = &tp
) TA
) TB
) TC
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
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)
WITH tmp1 AS (
SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1,
regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2,
to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3,
c1 FROM tmp
),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a)
SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE
z_c2='0000' AND z_c3=z_c4
CREATE TABLE tmp
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
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)
WITH tmp1 AS (
SELECT regexp_substr(to_char(c1),'[0]+')AS z_c1,
regexp_substr(to_char(c1),'[0]+[^0]')AS z_c2,
to_number(regexp_substr(to_char(c1),'[^0]\d+')) AS z_c3,
c1 FROM tmp
),tmp2 AS (SELECT a.*, MIN(z_c3) OVER(PARTITION BY z_c2 ) AS z_c4 FROM tmp1 a)
SELECT z_c1||to_char(z_c4+1) FROM tmp2 WHERE
z_c2='00002' AND z_c3=z_c4
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
UNION ALL
SELECT '000021' c1 FROM dual
UNION ALL
SELECT '000028' c1 FROM dual)WITH tmp1 AS (
SELECT a.*,LENGTH('00002') AS in_lenght FROM tmp a
),tmp2 AS (
SELECT c1,SUBSTR(c1,1,in_lenght) AS c1_z1,to_number(SUBSTR(c1,in_lenght+1,LENGTH(c1))) c1_z2, LENGTH(c1)-in_lenght c1_lenght FROM tmp1
),tmp3 AS (SELECT c1_z1,c1_z2,MIN(c1_z2) OVER(PARTITION BY c1_z1) c1_min,c1_lenght FROM tmp2)
--SELECT * FROM tmp3;
SELECT c1_z1||LPAD(c1_min+1,c1_lenght,0) FROM tmp3
WHERE c1_z1='00002' AND c1_min=c1_z2;
(SELECT '003002' 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 min(c2) from (
select c1,'003'||lpad(rownum,3,'0') c2 from t
where substr(c1,1,3)='003')--‘003’作为变量
where c1<>c2不过,2楼也可以完善的。一看各位就是在周围也懂sql的地方。我这里,一次我写分析函数,用了4分钟跑完了。一个博士说,这个程序是拿给别人看的,你这个人家看不懂,你要用最简单的sql写。最后,写plsql,跑完需要4个小时。但是客户表示很满意。
select min(key) mk from
(select key a,lead(key,1,null) b over(order by key) from
(selet key from tab where key like input% order by key)) tab where tab.b is null结果=mk+1.先找到所有符合输入条件的KEY 然后查询出这些KEY和与其对应的下一条记录,没有的补空。然后查询出条件是空的记录里最小的KEY。也不知道对否,刚来个新公司木有按数据库还无法尝试
with a as
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select t2.rk,t2.c
from (select t.*, rownum rn
from (select rank() over(partition by c order by rownum) rk, a.*
from a
where a.c like '003%') t) t2
where t2.rn = t2.rk;
KEY1 VARHCAR2(6)
KEY2 VARCHAR2(6)数据上KEY2的值全部是补0满6位的数值,但是存在不连续的,例如
003001
003003
003004
004011
004013现在要通过SQL实现用户输入003我就要找到最小的空号“003002”,用户输入00401我就要找到“004012”select
c.KEY2
from
(select KEY2 from t
where instr(KEY2 ,'&UserInput') = 1
order by KEY2 ) c
where rownum =1;不知道我写的符合你的要求不!
我有2年没有接触过数据库了
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select case when min(a.lv) is null then null else concat('00',min(a.lv)) end as "如果有空,测试空值,丢弃",
concat('00',min(a.lv)) as "输出不排除空"
from (select level lv
from (select min(to_number(c)) as nc, max(to_number(c)) as xc
from a
where c like '005%') t --var
where level >= t.nc
connect by level <= t.xc) a
where a.lv not in (select to_number(c) as c from a where c is not null);
with a as
(select '003001' as c
from dual
union all
select '003003' as c
from dual
union all
select '003004' as c
from dual
union all
select '004011' as c
from dual
union all
select '004013' as c
from dual)
select case
when min(c) is null then
null
else
concat('00', min(c) + 1)
end as xx
from (select c, next_c
from (select c, lead(c) over(order by c) next_c from a)
where c >= (select min(c) from a where a.c like '005%')
and next_c - c > 1);
V_OUTPUT VARCHAR(6);
V_MIN INT;
V_MAX INT;
V_EXE_SQL1 VARCHAR2(32767);
V_EXE_SQL2 VARCHAR2(32767);BEGIN V_INPUT := NVL(P_INPUT, '003'); V_EXE_SQL1 := ' SELECT MIN(TAIL) VALUE_MIN, MAX(TAIL) VALUE_MAX
FROM (WITH M AS (SELECT ''003001'' KEY2
FROM DUAL
UNION ALL
SELECT ''003003'' KEY2
FROM DUAL
UNION ALL
SELECT ''003004'' KEY2
FROM DUAL
UNION ALL
SELECT ''004011'' KEY2
FROM DUAL
UNION ALL
SELECT ''004013'' KEY2
FROM DUAL
UNION ALL
SELECT ''005993'' KEY2
FROM DUAL
UNION ALL
SELECT ''005995'' KEY2
FROM DUAL
UNION ALL
SELECT ''005996'' KEY2
FROM DUAL)
SELECT SUBSTR(KEY2, 0, 3) HEAD,
TO_NUMBER(SUBSTR(KEY2, 4, 3)) TAIL
FROM M
WHERE SUBSTR(KEY2, 0, 3) = :V1 ) '; V_EXE_SQL2 := '
SELECT MIN(T.RN)
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 999) T
WHERE T.RN >:V1 AND T.RN <:V2 '; EXECUTE IMMEDIATE V_EXE_SQL1
INTO V_MIN, V_MAX
USING V_INPUT; EXECUTE IMMEDIATE V_EXE_SQL2
INTO V_OUTPUT
USING V_MIN, V_MAX; V_OUTPUT := V_INPUT||trim(TO_CHAR(V_OUTPUT, '099'));
DBMS_OUTPUT.PUT_LINE(V_INPUT);
DBMS_OUTPUT.PUT_LINE(V_OUTPUT);END;
/
select to_char(nvl(max(c1) + 1, rpad(:inputstr, 6, '0') + 1), 'fm000000')
from t
start with c1 = to_char(rpad(:inputstr, 6, '0') + 1, 'fm000000')
connect by to_char(prior c1 + 1) = c1
客户不是应该更关注性能吗? 难道你们的业务是帮别人写SQL?
客户不是应该更关注性能吗? 难道你们的业务是帮别人写SQL?
那次是出去进行算法交流