初始化数据
DROP TABLE IF EXISTS X;
CREATE TABLE X(
a INT UNSIGNED PRIMARY KEY,
b CHAR(1) NOT NULL )ENGINE=INNODB; INSERT INTO X SELECT 3, 'a';
INSERT INTO X SELECT 4, 'b';
INSERT INTO X SELECT 6, 'c';
INSERT INTO X SELECT 7, 'd'; SELECT * FROM X;
假设a列从1开始，对于当前表中的数据3、4、6、7，查询应该返回1。如果当前表中的数据位1、2、3、4、6、7，查询结果应该返回5。
最小缺失值可以通过如下的表达式来解决：
SELECT
CASE
WHEN NOT EXISTS(SELECT * FROM X WHERE a=1) THEN 1
ELSE
(.......... 返回最小缺失值的子查询 ..........)
END AS missing;
如果表中不存在列a的值为1的情况，则返回1，反则返回子查询的结果，该子查询返回最小缺失值。
下面是通过子查询得到最小缺失值的过程：
SELECT MIN(a)+1 AS missing
FROM X A
WHERE NOT EXISTS(
SELECT * FROM X B WHERE A.a+1=B.a
);
整个解决方案的sql如下：
SELECT
CASE
WHEN NOT EXISTS(SELECT * FROM X WHERE a=1) THEN 1
ELSE
(SELECT MIN(a)+1 AS missing
FROM X A
WHERE NOT EXISTS(
SELECT * FROM X B WHERE A.a+1=B.a
))
END AS missing;
如果对表X执行上述语句会得到1。若按如下方式向类a插入值1和后后，重新运行结果，则会得到5。
# 插入1、2
INSERT INTO X SELECT 1, 'z';
INSERT INTO X SELECT 2, 'x';
对最小缺失值进行补缺操作
INSERT INTO X
SELECT
CASE
WHEN NOT EXISTS(SELECT * FROM X WHERE a=1) THEN 1
ELSE
(SELECT MIN(a)+1 AS missing
FROM X A
WHERE NOT EXISTS(
SELECT * FROM X B WHERE A.a+1=B.a
))
END AS missing, 'p';