By

Contents

最小缺失值是另一个可用子查询解决的问题，一般应用EXISTS谓词。为了说明该问题，首先，创建并填充表x，过程如下:

mysql> create table xx(
    -> a int unsigned primary key,
    -> b char(1) not null)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into xx select 3,'a';

mysql> insert into xx select 4,'b'; 

mysql> insert into xx select 6,'c'; 

mysql> insert into xx select 7,'d';

注意，a列必须是一个正整数，所以这里的类型为INT UNSIGNED。最小缺失值的问题是，假设列a从1开始，对于当前表中的数据3、4、6、7，查询应该返回1.如果当前表的数据为1、2、3、4、6、7、9，则查询应该返回5。最小缺失值的问题可以通过如下的表达式来解决：

SELECT 
    CASE WHEN NOT EXISTS (SELECT A FROM XX WHERE A=1) THEN 1
    ELSE ( ....返回最小缺失值的子查询....）
    END

如果表中不存在列a的值为1的情况，则结果返回1，否则返回子查询的结果，该子查询返回最小缺失值。
下面是通过子查询得到的最小缺失值的过程。

mysql> select min(a)+1 as missing
    -> from xx as a
    -> where not exists(
    -> select * from xx as b
    -> where a.a+1=b.a);
+---------+
| missing |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

NOT EXISTS谓词用来判断列a的值是否存在一个连续的值，使得A.a+1=B.a。如果存在，表示连续值；如果不存在，则表示是缺失值。MIN函数用来返回最小的缺失值，如果要求返回最大缺失值，则用MAX函数。
整个解决方案的SQL语句如下：

SELECT 
CASE
    WHEN NOT EXISTS ( SELECT a FROM xx WHERE a=1 ) THEN 1
ELSE
    (SELECT MIN(a)+1 as missing
        FROM xx AS A
        WHERE NOT EXISTS
            (SELECT * FROM xx AS B
                 WHERE A.a+1=B.a))
END AS missing;

如果对表xx执行上述语句，会得到值1.

mysql> SELECT 
    -> CASE
    ->     WHEN NOT EXISTS ( SELECT a FROM xx WHERE a=1 ) THEN 1
    -> ELSE
    ->     (SELECT MIN(a)+1 as missing
    ->         FROM xx AS A
    ->         WHERE NOT EXISTS
    ->             (SELECT * FROM xx AS B
    ->                  WHERE A.a+1=B.a))
    ->     END AS missing;
+---------+
| missing |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql>

若按如下方式向列a插入值1和2之后，重新运行上述查询，则会得到结果5.

mysql> insert into xx select 1,'z';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 2,'x'; 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT 
    -> CASE
    ->     WHEN NOT EXISTS ( SELECT a FROM xx WHERE a=1 ) THEN 1
    -> ELSE
    ->     (SELECT MIN(a)+1 as missing
    ->         FROM xx AS A
    ->         WHERE NOT EXISTS
    ->             (SELECT * FROM xx AS B
    ->                  WHERE A.a+1=B.a))
    ->     END AS missing;
+---------+
| missing |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

mysql>

若要对最小缺失值进行补缺操作，则可以通过INSERT…SELECT语句来实现，如：

INSERT INTO xx
SELECT 
CASE
    WHEN NOT EXISTS ( SELECT a FROM xx WHERE a=1 ) THEN 1
ELSE
    (SELECT MIN(a)+1 as missing
        FROM xx AS A
        WHERE NOT EXISTS
            (SELECT * FROM xx AS B
                 WHERE A.a+1=B.a))
END AS missing,
'p';

运行完上述语句再执行下面的语句查询表x，最终会得到如表4-15所示的结果。注意操作生成了工为5的列，它就是我们补缺的值。

mysql> select * from xx;
+---+---+
| a | b |
+---+---+
| 1 | z |
| 2 | x |
| 3 | a |
| 4 | b |
| 5 | p |
| 6 | c |
| 7 | d |
+---+---+
7 rows in set (0.00 sec)
Contents