一个表的主键 id 为1,2,3,5,6,7,8,10...依次增长的,为了表的id 能充分利用,用一条select 语句查出没有使用的id 号,例如id 为 1,2,3,5,6,7,9,10中有4,和8,没有使用,查处4,8.表结构:create table A(
id int primary key,
b varchar(20),
c varchar(50)
)
id int primary key,
b varchar(20),
c varchar(50)
)
minus
select id from a
DROP TABLE crm.test_A;
create table crm.test_A(
id int primary key,
b varchar(20)
);
INSERT INTO crm.test_A VALUES(1,'aa');
INSERT INTO crm.test_A VALUES(2,'bb');
INSERT INTO crm.test_A VALUES(3,'cc');
--INSERT INTO crm.test_A VALUES(4,'dd');
INSERT INTO crm.test_A VALUES(5,'ee');
INSERT INTO crm.test_A VALUES(6,'ff');
INSERT INTO crm.test_A VALUES(7,'gg');
--INSERT INTO crm.test_A VALUES(8,'hh');
INSERT INTO crm.test_A VALUES(9,'ii');
INSERT INTO crm.test_A VALUES(10,'jj');
COMMIT;--查询语句
SELECT ID1
FROM (SELECT ROWNUM ID1, A.ID, A.B FROM CRM.TEST_A A) B
WHERE NOT EXISTS (SELECT ID FROM CRM.TEST_A C WHERE B.ID1 = C.ID);
SELECT rn FROM (SELECT rownum rn FROM A) WHERE rn NOT IN (SELECT id FROM A)