Exists练习查找最小缺失值(Missing Value)

第一种方法
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
  keycol  INT         NOT NULL PRIMARY KEY CHECK(keycol > 0),
  datacol VARCHAR(10) NOT NULL
);

-- Minimum missing value query
SELECT MIN(A.keycol) + 1 as missing
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (SELECT * FROM dbo.T1 AS B
   WHERE B.keycol = A.keycol + 1)


SELECT
  CASE
    WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
    ELSE (SELECT MIN(A.keycol) + 1
          FROM dbo.T1 AS A
          WHERE NOT EXISTS
            (SELECT * FROM dbo.T1 AS B
             WHERE B.keycol = A.keycol + 1))
  END;

 例：重用被删除键值的方法
INSERT INTO dbo.T1(keycol, datacol)
  SELECT 
    CASE
      WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
      ELSE (SELECT MIN(A.keycol) + 1
            FROM dbo.T1 AS A
            WHERE NOT EXISTS
              (SELECT * FROM dbo.T1 AS B
               WHERE B.keycol = A.keycol + 1))
    END,
    'g';

注：NOT EXISTS谓词只为T1中间断之前的值（在这个例子中是4和7）返回TRUE。如果一个值加上1后不位于同一个表中，那么这人值正好位于一个间断的前面。 



-- Populating T1 with more rows
INSERT INTO dbo.T1(keycol, datacol) VALUES(1, 'e'),(2, 'f');

-- Embedding the CASE expression in an INSERT SELECT statement
INSERT INTO dbo.T1(keycol, datacol)
  SELECT 
    CASE
      WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
      ELSE (SELECT MIN(A.keycol) + 1
            FROM dbo.T1 AS A
            WHERE NOT EXISTS
              (SELECT * FROM dbo.T1 AS B
               WHERE B.keycol = A.keycol + 1))
    END,

-- Examining the content of T1 after the INSERT
SELECT * FROM dbo.T1;

第二种方法
-- Merging the two cases into one query
SELECT COALESCE(MIN(A.keycol) + 1, 1)
FROM dbo.T1 AS A
WHERE
  NOT EXISTS(
    SELECT * FROM dbo.T1 AS B
    WHERE B.keycol= A.keycol + 1)
  AND  EXISTS(
    SELECT * FROM dbo.T1
    WHERE keycol = 1);
GO

见议使用第一种方法：简单、直观、可读性更高

在数据处理时，我们经常会使用一些“自增”的插入方式来处理数据。比如学生学号：B07051001，B07051002....类似的递增关系的数据。

但是，如果中途因为某些原因将其中的一些记录删除掉之后，就会出现断续的记录。这时，我们可能期待将这些中间的缺失值再次利用。以下，就谈谈如何查找最小缺失值。

首先，我们建一个测试表：tb_Test(主键并未设置为自增长):

[c-sharp] view plaincopyprint?
01.create table tb_Test  
02.(  
03.    id int primary key,  
04.    val char(1) null  
05.)  
create table tb_Test
(
	id int primary key,
	val char(1) null
) 

 

插入一些数据：

[c-sharp] view plaincopyprint?
01.insert into tb_Test values(1,'a')  
02.insert into tb_Test values(2,'b')  
03.insert into tb_Test values(3,'c')  
04.insert into tb_Test values(4,'d')  
05.insert into tb_Test values(5,'e')  
06.insert into tb_Test values(6,'f')  
07.insert into tb_Test values(7,'g')  
08.insert into tb_Test values(8,'h')  
insert into tb_Test values(1,'a')
insert into tb_Test values(2,'b')
insert into tb_Test values(3,'c')
insert into tb_Test values(4,'d')
insert into tb_Test values(5,'e')
insert into tb_Test values(6,'f')
insert into tb_Test values(7,'g')
insert into tb_Test values(8,'h') 

 

删除某些记录，制造“断层”：

delete from tb_Test where id in (1,2,4,5,7);

 

此时表中数据为不连贯的：



此时能看出最小缺失值应该为：1

 

我们通过下面这段sql能够得到结果：

select 
   case 
      when not exists(select 1 from tb_Test where id=1) 

      then 1
      else (
         select min(a.id+1) 
           from tb_Test as a
        where not exists 
        (
          select 1 
            from tb_Test as b
         where b.id=a.id+1
        )
     ) 
  end as '最小缺失值';

 

 这里使用了一个小的技巧，原理是将表中所有记录的id加1，再与源表中所有记录的id匹配。这样只要有源表中有id缺失，id+1在源表中就会有匹配不到的值。

比如源表中id序列为：1、2、3、5、7(a.id与b.id)，则源表中的id+1序列为： 2、3、4、6、8(a.id+1)；

这样再代入子查询中，就可以看到a.id+1=4,和a.id+1=6和a.id+1=8在b.id中不存在匹配值。然后再去最小值:min()这样结果就为4。

 

但是以上上图中的这个序列3,6,8用子查询得出的结果也应该为4，而正确答案为1，显然只是用子查询这样的方式处理是不完整的。

 

那为什么要把1单独判断呢？这是由1的位置的特殊性决定的。因为1开始时总是处在序列的最前端的位置(正常情况下)。它的前面已经没有数字了，也就是说不存在a.id+1=1(因为我们默认序列是从1开始增长的)。因此没有哪个数字存在与否能判断出1是否存在。所以1需要单独考虑。

 

处于同样的原理，我们可以用这种方式重用被删除的键：

只要在前面加上：insert into ti_Test(id,val) Select .....(同上)即可。

 

当然你可以使用coalesce函数来合并，存在1和不存在1的情况：

如下：

 

 select Coalesce(Min(a.id+1),1)

   from tb_Test a

where not exists (
 select 1 
   from tb_Test as b
where b.id=a.id+1
) And exists(select 1 from tb_Test where id=1)

注：coalesce函数用于返回第一个非空值。也就是说如果序列中没有1，在被where筛选器筛选后，返回的值为null,此时min(a.id+1)也为null，这样返回的结果就为1。

 

最后，并不推荐重用返回值并且在多线程运行时也可能得到重复的键