最小缺失值(Exists,COALESCE)

最新推荐文章于 2022-07-19 22:15:46 发布

bradleydan

最新推荐文章于 2022-07-19 22:15:46 发布

阅读量442

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 文章标签： insert null table object go c

本文链接：https://blog.youkuaiyun.com/bradleydan/article/details/7950335

SQL Server 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了使用SQL创建表、插入数据以及通过SQL查询和处理最小缺失值的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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