最小缺失值(Exists,COALESCE)

1. 创建表

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
);
INSERT INTO dbo.T1(keycol, datacol) VALUES(3, 'a');
INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');
INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');
INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');

2. 查询最小缺失值

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;

查询最小缺失值方法二

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

3. 插入缺失值

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,
    'f';