主页 / dba / 问题 / 330900
Accepted
Nikolay Yasinskiy
Asked: 2023-09-05 18:06:42 +0800 CST

在 GRASS GIS 中 db.execute IN 语句有效，但 NOT IN 无效

  • 772

这是我的 GRASS GIS 数据库命令（使用 SQL Lite 驱动程序）：

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream IN (SELECT number FROM streams_order)"

但我想要

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream NOT IN (SELECT number FROM streams_order)"

结果应该是：

+---------+--------------+--------+---------+
| stream  |  next_stream   factor |  number |
+-------------------------------------------+
  370           -1           
  372           370         370       372
  374           440         
  376           372                   376
  380           374         374       380
.............................................

它无法在没有任何错误的情况下工作：运行命令后“factor”列为空。如果我放进去效果很好。列类型均为 INT。

包含真实数据的沙箱在这里https://dbfiddle.uk/zg8_xJUz

sqlite

2 个回答

  1. 如果支持 IN，那么 NOT IN 也应该支持

    如果某些内容不受支持，您将面临一些错误，就像您在上一个问题DBMI-SQLite driver error: Error in sqlite3_prepare(): close ".": syntax error in db.execute command 中遇到的那样。

    要在运行更新语句之前调试问题，请运行 select。

    如果我运行在 select 语句中转换的相同更新查询，您将看到没有返回任何记录。

    select factor2_new,next_stream,number 
from stream_order
WHERE number IS NOT NULL AND next_stream NOT IN (SELECT number FROM stream_order)

    现在问题出在查询上。

    我建议使用not exists而不是not in，请参阅SQL“选择不在子查询中的位置”对于存在和 in 之间的差异不返回任何结果。

    将您的选择查询更改为，

    SELECT  *
FROM  stream_order
WHERE number IS NOT NULL 
AND  NOT EXISTS (SELECT  NULL
                 FROM    stream_order so
                 WHERE   so.number = stream_order.next_stream )

    结果，

    stream  next_stream factor  factor2 next_stream2    number  factor_new  factor2_new next2
  111      222      null       -1       -1           333        null    null    null
  370      -1       null       -1       -1           370        null    null    null
  398      394      null       394      394          398        null    null    null
  404      402      null       402      402          404        null    null    null
  420      416      null       416      416          420        null    null    null
  458      454      null       454      454          458        null    null    null

    更新查询，

    UPDATE stream_order
SET factor2_new=next_stream 
WHERE number IS NOT NULL 
AND  NOT EXISTS (SELECT  NULL
                 FROM    stream_order so
                 WHERE   so.number = stream_order.next_stream )

    请参阅此处的示例

    • 2
  2. Best Answer

    您的问题与列中存在 NULL 值有关number。

    当列表具有 NULL 值时，NOT IN 总是失败，因为 NULL 比较不能为 true 或 false。

    更准确地说，在 SQL 逻辑中 NULL 并不意味着“空”，它意味着“未确定”。val = NULL即使 val 也是 NULL，比较结果也始终为 NULL。这就是为什么要检查某些内容是否为 NULL，我们需要使用特殊运算符IS NULL。

    该表达式val IN ('A', NULL)将被翻译为val='A' OR val=NULL.

    如果 val 等于 'A' 将变为TRUE OR NULL，其计算结果为TRUE。（并且 NOT IN 的计算结果为FALSE）。但如果 val 等于 'B'，则表达式变为FALSE OR NULL，其计算结果为NULL（并且 NOT IN 仍然是NULL）。

    因此，在包含 null 元素的列表中，val IN (...)如果非空元素之一等于 val，则 will 计算结果为 TRUE，否则为 NULL，而val NOT IN (...)如果非空元素之一等于 val 且否则为 NULL，则 while 计算结果为 FALSE，但它会永远不会评估为 TRUE，因此您的更新永远不会触发。

    另请参阅这个问题。

    解决方法：

    UPDATE streams_order SET factor=next_stream 
WHERE number IS NOT NULL AND 
      next_stream NOT IN (SELECT number FROM streams_order WHERE number IS NOT NULL)
    • 1

相关问题