MySQL - 从数字列表中选择在表的 id 字段中没有对应项的数字

Question

我有一个数字列表，例如 {2,4,5,6,7} 我有一个表 foos，带有 foos.ID，包括 {1,2,3,4,8,9}

我想获取我的数字列表，并找到那些在我的表的 ID 字段中没有对应项的数字。

实现此目的的一种方法是创建一个表格栏，在 ID 字段中加载 {2,4,5,6,7}。 然后，我会做

SELECT bars.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

但是，我想在没有临时表的情况下完成这个任务。

有人对它如何发生有任何意见吗？

Answer 1

这是一个非常常见的问题：动态生成关系而不创建表。 对于这个问题的 SQL 解决方案非常尴尬。 一个使用派生表的示例：

SELECT n.id
FROM
  (SELECT 2 AS id 
   UNION SELECT 3 
   UNION SELECT 4 
   UNION SELECT 5 
   UNION SELECT 6 
   UNION SELECT 7) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

但这并不能很好地扩展，因为您可能有很多值，而不仅仅是六个。 构造一个长列表，其中每个值都需要一个 UNION ，这可能会变得很烦人。

另一种解决方案是手头保留一个十位数字的通用表格，并将其重复用于多种目的。

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT n.id
FROM 
  (SELECT n1.i + n10.i*10 AS id
   FROM num AS n1 CROSS JOIN num AS n10
   WHERE n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

我显示了内部查询生成从 0..99 开始的值，尽管对于本例来说这不是必需的。 但列表中的值可能大于 10。 要点是，使用一个表 num，您可以生成大量数字，而不必求助于每个值一个 UNION 的很长的链。 此外，您可以在一处指定所需值的列表，这样更方便且更具可读性。

Answer 2

我找不到不使用临时表的精确问题的解决方案，但是使用子选择而不是联接进行查询的另一种方法是：

SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)

像我最初写的其他海报一样：

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

但后来我意识到这会产生与你想要的相反的结果。

Answer 3

如果您使用 PHP，则无需创建任何临时表即可完成此工作。

SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)

您可以使用 PHP 的 array_diff() 函数将其转换为所需的结果。 如果您的列表 (2,4,5,6,7) 位于名为 $list 的数组中，并且上面查询的结果位于数组 $result 中，那么

$no_counterparts = array_diff($list, $result);

...将返回列表中的所有数字，没有对应项在你的数据库表中。 虽然此解决方案不会在查询中执行整个操作，但您需要在 PHP 中执行的后处理是最少的，以获得您想要的结果，并且避免创建临时表可能是值得的。

Answer 4

我有类似的问题。 我有一个范围，其中自动递增主键有一些缺失值，所以首先我发现有多少个值：
从 nid > 的节点中选择 count(*) 1962 年。
将此数字与最高值进行比较，我发现该数字丢失了。 然后我运行了这个查询：
从节点 n1 中选择 n2.nid 在 n1.nid = (n2.nid - 1) 上右连接节点 n2，其中 n1.nid 为 null 并且 n2.nid > 1962
这将找到非连续缺失记录的数量。 它不会显示连续的，而且我不完全确定如何做到这一点，除了更改 ON 子句以允许更大的纬度（这将使 JOIN 表变得更大）。
无论如何，这给了我总共七个缺失结果中的五个结果，另外两个保证至少与五个结果之一相邻。 如果失踪人数较多，您可能需要其他方法来查找剩余的失踪人员。

Answer 5

Alnitak 的（和你的）解决方案应该可以工作，我无法想象其他只能在 SQL 语言中工作的解决方案。

但问题来了——如何传递值列表？ 在调用代码中处理这个问题不是更好吗 - 即请求 ID 并在排序代码中进行比较，排序代码可能采用更适合这种操作的语言。

Answer 6

碰巧来到这里寻找答案。 之前的帖子是 MySQL 8 之前的文章。由于 MySQL 从 8.0.19 版本开始就有 value 语句，因此可以使用 value 语句和 CTE 非常优雅地解决这个问题。 code> 从 MySQL 8.0 开始也可用

第 1 步：
将 CTE 和 value 语句结合起来，创建一组行，其中包含需要与表（此处表为 foo）进行比较的值。

with MyValues(val) as
(
  values row(2),row(4),row(5),row(6),row(7)
)

第 2 步：
过滤 CTE 中具有空值的行，

WITH myvalues(val)
     AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
SELECT f.id
FROM   foo f
       LEFT OUTER JOIN myvalues m
                    ON f.id = m.val
WHERE  m.val IS NULL; 

将 CTE 与表 foo 进行外连接，并在外连接后使用 foo Trail

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    ->        LEFT OUTER JOIN myvalues m
    ->                     ON f.id = m.val
    -> WHERE  m.val IS NULL;
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)

或者使用IN 子句

mysql> WITH myvalues(val)
    ->      AS (VALUES ROW(2), ROW(4), ROW(5), ROW(6), ROW(7))
    -> SELECT f.id
    -> FROM   foo f
    -> WHERE  id NOT IN (SELECT val
    ->                   FROM   myvalues);
+------+
| id   |
+------+
|    1 |
|    3 |
|    8 |
|    9 |
+------+
4 rows in set (0.00 sec)