1、创建一个表chkdata CREATE TABLE chkdata(a NUMBER(5));2、向表中插入从0到99999,共10万条数据。 BEGIN FOR i IN 0 .. 99999 LOOP INSERT INTO chkdata VALUES(i); COMMIT; END; /3、执行查询 SELECT c.a FROM chkdata c LEFT OUTER JOIN maptab m ON(c.a=TO_NUMBER(SUBSTR(m.id,4))) WHERE m.id IS NULL;
1、查询ID最大值,计为MAX_ID SELECT MAX(ID) FROM TB 2、找个记录数大于该最大值的表TB_B 3、构造查询语句SELECT B.STD_ID, A.ID FROM (SELECT SUBSTR(ID,4) AS ID FROM TB) A, (SELECT LTRIM(TO_CHAR(ROWNUM, '000000')) AS STD_ID FROM TB_B WHERE (ROWNUM < MAX_ID + 1)) B WHERE A.ID (+) = B.STD_ID ORDER BY A.ID DESC
SQL> SELECT * FROM TMP ORDER BY ID; ID NAME ---------- ---------- 1 A 4 A 6 A 8 A 10 BSQL> DECLARE 2 MINV NUMBER; 3 MAXV NUMBER; 4 I NUMBER; 5 ICOUNT NUMBER; 6 BEGIN 7 SELECT MIN(ID) INTO MINV FROM TMP; 8 SELECT MAX(ID) INTO MAXV FROM TMP; 9 FOR I IN MINV..MAXV 10 LOOP 11 SELECT COUNT(1) INTO ICOUNT FROM TMP WHERE ID=I; 12 IF ICOUNT=0 THEN 13 DBMS_OUTPUT.PUT_LINE(I); 14 END IF; 15 END LOOP; 16 END; 17 / 2 3 5 7 9PL/SQL procedure successfully completed.
1、查询ID最大值,计为MAX_ID SELECT MAX(ID) FROM TB 2、找个记录数大于该最大值的表TB_B--->此表的记录数要大于MAX_ID,即select count(1) from TB_B ;取得记录数要大于MAX_ID,只要符合这个条件的任何一个表都可以 3、构造查询语句SELECT A.ID FROM (SELECT SUBSTR(ID,4) AS ID FROM TB) A WHERE A.ID NOT IN (SELECT LTRIM(TO_CHAR(ROWNUM, '000000')) FROM TB_B WHERE (ROWNUM < MAX_ID + 1))
SELECT A.ID1 FROM (SELECT LTRIM(TO_CHAR(ROWNUM, '000000')) AS ID1 FROM TB_B WHERE (ROWNUM < MAX_ID + 1)) A WHERE A.ID1 NOT IN (SELECT SUBSTR(ID,4) FROM TB) 上面的写反了 sorry~
SQL> SELECT * FROM TMP ORDER BY ID; ID NAME ---------- ---------- 1 A 4 A 6 A 8 A 10 BSQL> DECLARE 2 MINV NUMBER; 3 MAXV NUMBER; 4 I NUMBER; 5 ICOUNT NUMBER; 6 BEGIN 7 SELECT MIN(ID) INTO MINV FROM TMP; 8 SELECT MAX(ID) INTO MAXV FROM TMP; 9 FOR I IN MINV..MAXV 10 LOOP 11 SELECT COUNT(1) INTO ICOUNT FROM TMP WHERE ID=I; 12 IF ICOUNT=0 THEN 13 DBMS_OUTPUT.PUT_LINE(I); 14 END IF; 15 END LOOP; 16 END; 17 / -------------------------- 方法不错,怎么把这个写成存储过程呢?我只要得到DBMS_OUTPUT.PUT_LINE(I);里最小的一个值
CREATE TABLE chkdata(a NUMBER(5));2、向表中插入从0到99999,共10万条数据。
BEGIN
FOR i IN 0 .. 99999 LOOP
INSERT INTO chkdata VALUES(i);
COMMIT;
END;
/3、执行查询
SELECT c.a
FROM chkdata c
LEFT OUTER JOIN maptab m
ON(c.a=TO_NUMBER(SUBSTR(m.id,4)))
WHERE m.id IS NULL;
SELECT MAX(ID) FROM TB
2、找个记录数大于该最大值的表TB_B
3、构造查询语句SELECT B.STD_ID, A.ID
FROM (SELECT SUBSTR(ID,4) AS ID
FROM TB) A,
(SELECT LTRIM(TO_CHAR(ROWNUM, '000000')) AS STD_ID
FROM TB_B
WHERE (ROWNUM < MAX_ID + 1)) B
WHERE A.ID (+) = B.STD_ID
ORDER BY A.ID DESC
SQL> SELECT * FROM TMP ORDER BY ID; ID NAME
---------- ----------
1 A
4 A
6 A
8 A
10 BSQL> DECLARE
2 MINV NUMBER;
3 MAXV NUMBER;
4 I NUMBER;
5 ICOUNT NUMBER;
6 BEGIN
7 SELECT MIN(ID) INTO MINV FROM TMP;
8 SELECT MAX(ID) INTO MAXV FROM TMP;
9 FOR I IN MINV..MAXV
10 LOOP
11 SELECT COUNT(1) INTO ICOUNT FROM TMP WHERE ID=I;
12 IF ICOUNT=0 THEN
13 DBMS_OUTPUT.PUT_LINE(I);
14 END IF;
15 END LOOP;
16 END;
17 /
2
3
5
7
9PL/SQL procedure successfully completed.
SELECT MAX(ID) FROM TB
2、找个记录数大于该最大值的表TB_B--->此表的记录数要大于MAX_ID,即select count(1) from TB_B ;取得记录数要大于MAX_ID,只要符合这个条件的任何一个表都可以
3、构造查询语句SELECT A.ID
FROM (SELECT SUBSTR(ID,4) AS ID FROM TB) A
WHERE A.ID NOT IN (SELECT LTRIM(TO_CHAR(ROWNUM, '000000'))
FROM TB_B
WHERE (ROWNUM < MAX_ID + 1))
TB_B是你随便找的一个表,但要保证该表的记录数大于等于TB(即你ID的那个表)中SUBSTR(ID,4)的最大值,目的是产生一个标准的连续的序列
FROM (SELECT LTRIM(TO_CHAR(ROWNUM, '000000')) AS ID1
FROM TB_B
WHERE (ROWNUM < MAX_ID + 1)) A
WHERE A.ID1 NOT IN (SELECT SUBSTR(ID,4) FROM TB)
上面的写反了
sorry~
SQL> SELECT * FROM TMP ORDER BY ID; ID NAME
---------- ----------
1 A
4 A
6 A
8 A
10 BSQL> DECLARE
2 MINV NUMBER;
3 MAXV NUMBER;
4 I NUMBER;
5 ICOUNT NUMBER;
6 BEGIN
7 SELECT MIN(ID) INTO MINV FROM TMP;
8 SELECT MAX(ID) INTO MAXV FROM TMP;
9 FOR I IN MINV..MAXV
10 LOOP
11 SELECT COUNT(1) INTO ICOUNT FROM TMP WHERE ID=I;
12 IF ICOUNT=0 THEN
13 DBMS_OUTPUT.PUT_LINE(I);
14 END IF;
15 END LOOP;
16 END;
17 /
--------------------------
方法不错,怎么把这个写成存储过程呢?我只要得到DBMS_OUTPUT.PUT_LINE(I);里最小的一个值