mysql - 如何从 MySQL 列中缺失的 orderred 值中获取最小值？

Question

这个问题已经有答案了:

How do I find a "gap" in running counter with SQL? (22 个回答)

已关闭 6 年前。

我在列 [1,2,3,4,6,7,9,11,12] 中有以下整数值，我想编写一个查询，给出最小正缺失整数，该整数应该是 5在这种情况下

谢谢

Answer 1

这是一种方法:

select min(t.col) + 1
from t
where not exists (select 1 from t t2 where t2.col = t.col + 1);

编辑:

如果我假设您想要“1”(如果序列中缺少它)，那么:

select (case when max(tm.mincol) > 1 then 1
             else min(t.col) + 1
        end) as first_missing
from t cross join
     (select min(col) as mincol, max(col) as maxcol
      from t
     ) tm
where not exists (select 1 from t t2 where t2.col = t.col + 1);