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

Question

我有一个数字列表，比如 {2,4,5,6,7}我有一个 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 的非常长的链。此外，您可以在一处指定所需值的列表，这样更方便和可读。