--建表
create table table_a (
aid number(10),
aname varchar2(20),
asex char(1)
);
--插入数据
insert into table_a values (1, 'jack' ,'1');
insert into table_a values (2, 'ruc' ,'1');
insert into table_a values (3, 'lucas' ,'0');
insert into table_a values (5, 'jet' ,'0');
insert into table_a values (6, 'ray' ,'0');
insert into table_a values (7, 'bill' ,'1');
insert into table_a values (9, 'mary' ,'0');
表结构:
aid aname asex
1 jack 1
2 ruc 1
3 lucas 0
5 jet 0
6 ray 0
7 bill 1
9 mary 0
找出表中aid缺少的最小和最大值
即答案是4,8;
这个是在某群中有人提出的问题,这里有一个陷阱,就是例子中的aid只缺了单个值,如果aid为5的记录被删除后,要保证答案依旧是4,8
我有参考论坛中某个帖子(忘记叫什么了,大概是说腾讯的sql面试题)
select min(t1.x), max(t1.x)
from (SELECT rownum x
FROM DUAL
connect by rownum < (select max(aa.aid) from table_a aa)) t1
where not exists (select 1 from table_a a where a.aid = t1.x);