有一张表TAB1,字段num。num里有很多数字,我想从1开始,查到里面缺少的最小的一个数字:例如 4,5,6,8,9,11,12,13;这样的话我想要的结果是1;1,2,3,4,5这样的话,我想要的结果是6;
其实利用正常排序的找第一个不正常递增的号码就行了,然后找出它的序号,用正常数值中最大值+1比它小的其实就行了。
create table TAB1(num varchar(32));
insert into TAB1 values(4);
insert into TAB1 values(5);
insert into TAB1 values(6);
insert into TAB1 values(8);
insert into TAB1 values(9);
insert into TAB1 values(11);
insert into TAB1 values(12);
insert into TAB1 values(13);
insert into TAB1 values(1);
insert into TAB1 values(4);
where num <(select MIN(num) as minbreak
from(select num,ROW_NUMBER() over(order by num) as sort
from (select distinct num from TAB1) temp1) temp2
where num <> sort);
SELECT coalesce (min (num1), count (1) + 1) FROM TAB1 --如果正好是顺序排列,找出缺少的最后一个值
LEFT JOIN (
(SELECT min (row_num) AS num1 --找出缺少的值
FROM (SELECT num
, row_NUMBER () over (ORDER BY num ASC) AS row_num
FROM TAB1) T1
WHERE t1.num != t1.row_num)
) T2 ON 1 = 1 ;
都可以。