主页 / dba / 问题 / 256232
Accepted
TMilliman
Asked: 2019-12-25 02:11:11 +0800 CST

理解不存在

  • 772

考虑下表，

CREATE TABLE GapsIslands (ID INT NOT NULL, SeqNo INT NOT NULL);

ALTER TABLE GapsIslands ADD CONSTRAINT pk_GapsIslands PRIMARY KEY (ID, SeqNo);

INSERT INTO GapsIslands
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 5 UNION ALL SELECT 1, 6 
UNION ALL SELECT 1, 8 UNION ALL SELECT 1, 9 UNION ALL SELECT 1, 10 UNION ALL SELECT 1, 12 
UNION ALL SELECT 1, 20 UNION ALL SELECT 1, 21 UNION ALL SELECT 1, 25 UNION ALL SELECT 1, 26;

我正在努力理解NOT EXISTS在差距和岛屿解决方案的背景下条件的应用。

以下查询旨在查找“岛屿”的起点：

SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
FROM GapsIslands AS a
WHERE NOT EXISTS ( 
    SELECT  * 
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1)

现在在 NOT EXISTS 条件下的查询，

SELECT  * 
    FROM GapsIslands AS b
    WHERE b.ID = a.ID AND b.SeqNo = a.SeqNo - 1

返回，列（让我们将此列称为 A）SeqNo 为：

 SeqNo      
  1     
  5     
  8     
  9     
  20        
  25

SeqNo 的“完整”列（我们称之为 B 列）是：

  SeqNo     
  1     
  2     
  5     
  6     
  8     
  9
  10        
  12        
  20        
  21        
  25
  26

那么，为什么当我们在 A 列中取 B 列值 WHERE NOT EXISTS 时，我们会找到该列吗？

 SeqNo      
  1     
  5     
  8     
  12        
  20        
  25

B列的值1也是IN列A，那么为什么值1会出现在查询的SeqNo列中呢？

sql-server

1 个回答

  1. Best Answer

    让我们将有问题的表达式从WHERE条件移动到SELECT输出列表。EXISTS给出布尔值，SQL server 不想直接显示，所以我们将使用CASE它并将其转换为可读形式。

    SELECT ID, SeqNo, ROW_NUMBER() OVER (ORDER BY SeqNo) AS RowNum
/*   There we insert our EXISTS moved from WHERE   */
/*              and wrapped into CASE              */
, CASE WHEN EXISTS ( SELECT  * 
                     FROM GapsIslands AS b
                     WHERE b.ID = a.ID 
                       AND b.SeqNo = a.SeqNo - 1 )
       THEN 'Exists'      /* EXISTS is true there, NOT EXISIS is false */
       ELSE 'Not exists'  /* NOT EXISTS is true there, EXISIS is false */
       END AS previous_SeqNo_exists
/* ----------------------------------------------- */
FROM GapsIslands AS a
ORDER BY SeqNo

    小提琴

    该查询为我们提供了下一个输出：

    身份证 | 序号 | 行号 | previous_SeqNo_exists
-: | ----: | :----- | :--------------------
 1 | 1 | 1 | 不存在           
 1 | 2 | 2 | 存在               
 1 | 5 | 3 | 不存在           
 1 | 6 | 4 | 存在               
 1 | 8 | 5 | 不存在           
 1 | 9 | 6 | 存在               
 1 | 10 | 7 | 存在               
 1 | 12 | 8 | 不存在           
 1 | 20 | 9 | 不存在           
 1 | 21 | 10 | 存在               
 1 | 25 | 11 | 不存在           
 1 | 26 | 12 | 存在

    似乎是绝对清楚的。SeqNo=1 没有先前的值，因为根本没有这样的记录，SeqNo=5 （以及其他所有） - 因为之前有一个间隙。

    当我们在“Exists”的记录中使用这个EXISTS表达式时，WHERE将被删除，我们将只获得“不存在”的记录（不要忘记还有额外的NOT运算符，所以只会FALSE返回给定的记录）。

    ROW_NUMBER()，它在 之后起作用，它WHERE简单地以给定的顺序枚举返回的记录。

    PS。正在考虑的查询（由 OP 发布）不包含 ORDER BY 子句。那很不好。这可能会导致记录以随机顺序返回。尽管如此，计算出的记录数ROW_NUMBER()将是正确的，因为该函数有其自己的本地排序。

    • 5

相关问题