ITPUB上碰到一个问题,觉得有点意思,顺便就在博客把解决思路列了下来。
原问题:
面试遇到一条SQL题,查询被删除的ID
大概意思是,有一张业务表,字段ID为关键字标识每一张业务单,ID数据类型为int,正常情况下,每增加一条记录,ID值增加1。
当表中某些记录被删除后,ID就会存在断裂;如:表中原来10条记录,当2,4,6 被删除后,表中的内容如下:
—————————
| ID |
—————————
| 1 |
—————————
| 3 |
—————————
| 5 |
—————————
| 7 |
—————————
| 8 |
—————————
| 9 |
—————————
| 10 |
—————————
增加记录时,查询ID没有被使用最小值,上例的查询结果应该为2。
这个SQL如何写呢?
个人解决思路如下:
建立测试表:
create table t (n number);
insert into t values(1);
insert into t values(3);
insert into t values(5);
insert into t values(7);
insert into t values(8);
insert into t values(9);
insert into t values(10);
commit;
select * from t;
SQL写法如下:
with s as (
select 0 n,1 m from dual –建立connect by的初始源
union
select n,n+1 m from t –取出t表的下一ID
),
r as (
select s.*,level from s
start with n=0 –从0开始connect by,出现断层,即所需的ID号
connect by n=prior m )
select max(n)+1 from r;