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

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

我想拿我的数字列表，并在我的表格的 ID 字段中找到没有对应的数字.

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

<上一页>SELECT bar.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

但是，我想完成这个无临时表.

有人对它可能发生的情况有任何意见吗?

解决方案

这是一个很常见的问题:在不创建表的情况下动态生成关系.这个问题的 SQL 解决方案非常尴尬.使用派生表的一个示例:

SELECT n.id从(选择 2 作为 id联合选择 3联合选择 4联合选择 5联合选择 6联合选择 7) 作为 n左外连接 foos 使用 (id)WHERE foos.id 为空；

但这不能很好地扩展，因为您可能有很多值而不是只有六个.构建一个长长的列表，每个值需要一个 UNION 会变得很烦人.

另一种解决方案是保留一个十位数的通用表格，并重复使用它以用于多种目的.

CREATE TABLE num (i int);INSERT INTO num (i) 值 (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);选择 n.id从(选择 n1.i + n10.i*10 作为 IDFROM num AS n1 交叉连接 num AS n10其中 n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n左外连接 foos 使用 (id)WHERE foos.id 为空；

我展示了从 0..99 生成值的内部查询，即使在这种情况下这不是必需的.但是您的列表中的值可能大于 10.关键是，使用一个表 num，您可以生成大量数字，而不必求助于每个值一个 UNION 的非常长的链.此外，您可以在一处指定所需值的列表，这样更方便和可读.

I have a list of numbers, say {2,4,5,6,7} I have a table, foos, with foos.ID, including say, {1,2,3,4,8,9}

Id like to take my list of numbers, and find those without a counterpart in the ID field of my table.

One way to achieve this would be to create a table bars, loaded with {2,4,5,6,7} in the ID field. Then, I would do

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

However, I'd like to accomplish this sans temp table.

Anyone have any input on how it might happen?

解决方案

This is a problem that is pretty common: generating a relation on the fly without creating a table. SQL solutions for this problem are pretty awkward. One example using a derived table:

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;

But this doesn't scale very well, because you might have many values instead of just six. It can become tiresome to construct a long list with one UNION needed per value.

Another solution is to keep a general-purpose table of ten digits on hand, and use it repeatedly for multiple purposes.

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;

I show the inner query generating values from 0..99 even though this isn't necessary for this case. But you might have values greater than 10 in your list. The point is that with one table num, you can generate large numbers without having to resort to very long chains with one UNION per value. Also, you can specify the list of desired values in one place, which is more convenient and readable.