测试数据:CREATE TABLE T185 ( F1 NUMBER(2) );INSERT INTO T185 VALUES(1); INSERT INTO T185 VALUES(2); INSERT INTO T185 VALUES(3); INSERT INTO T185 VALUES(4); INSERT INTO T185 VALUES(5); INSERT INTO T185 VALUES(6); INSERT INTO T185 VALUES(7); INSERT INTO T185 VALUES(9); INSERT INTO T185 VALUES(10); INSERT INTO T185 VALUES(11); 测试结果:
select id-1 from test a where id not in(select 1+id from test where id not in(select max(id) from test)) and id not in (select min(id) from test)
select id-1 from test a where id not in(select 1+id from test) and id not in (select min(id) from test)
(
F1 NUMBER(2)
);INSERT INTO T185 VALUES(1);
INSERT INTO T185 VALUES(2);
INSERT INTO T185 VALUES(3);
INSERT INTO T185 VALUES(4);
INSERT INTO T185 VALUES(5);
INSERT INTO T185 VALUES(6);
INSERT INTO T185 VALUES(7);
INSERT INTO T185 VALUES(9);
INSERT INTO T185 VALUES(10);
INSERT INTO T185 VALUES(11);
测试结果:
select id-1 from test a
where id not in(select 1+id from test
where id not in(select max(id) from test)) and id not in (select min(id) from test)
select id-1 from test a
where id not in(select 1+id from test) and id not in (select min(id) from test)