MySQL - 从表的 id 字段中没有对应项的数字列表中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/273623/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Select from a list of numbers those without a counterpart in the id field of a table
提问by SocialCensus
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}
我有一个数字列表，比如 {2,4,5,6,7} 我有一个表，foos，带有 foos.ID，包括说，{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.
我想拿我的数字列表，在我的表的 ID 字段中找到那些没有对应的数字。
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
实现此目的的一种方法是创建一个表格栏，在 ID 字段中加载 {2,4,5,6,7}。然后，我会做
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?
任何人都对它可能如何发生有任何意见？
回答by Bill Karwin
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:
这是一个很常见的问题：在不创建表的情况下动态生成关系。这个问题的 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;
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 UNIONneeded per value.
但这不能很好地扩展，因为您可能有很多值，而不仅仅是六个。构建一个长列表，UNION每个值都需要一个列表会变得很烦人。
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 UNIONper value. Also, you can specify the list of desired values in one place, which is more convenient and readable.
我显示了从 0..99 生成值的内部查询，即使这在这种情况下不是必需的。但是您的列表中可能有大于 10 的值。关键是，使用一个 table num，您可以生成大量数字，而不必求助于UNION每个值一个的非常长的链。此外，您可以在一处指定所需值的列表，这样更加方便和可读。
回答by Alnitak
I can't find a solution to your precise problem that doesn't use a temporary table, but an alternate way of doing your query using a sub-select instead of a join is:
我找不到不使用临时表的精确问题的解决方案，但是使用子选择而不是连接进行查询的另一种方法是：
SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)
Like the other posters I originally wrote:
就像我最初写的其他海报一样：
SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)
but then I realised that this is producing the opposite to what you want.
但后来我意识到这与你想要的相反。
回答by Alex Matulich
If you use PHP, you can make this work without creating any temporary tables.
如果您使用 PHP，则无需创建任何临时表即可完成这项工作。
SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)
You can use PHP's array_diff() function to convert this to the desired result. If your list (2,4,5,6,7) is in an array called $list and the result of the query above is in an array $result, then
您可以使用 PHP 的 array_diff() 函数将其转换为所需的结果。如果您的列表 (2,4,5,6,7) 位于名为 $list 的数组中，而上述查询的结果位于数组 $result 中，则
$no_counterparts = array_diff($list, $result);
...will return all the numbers in your list with no counterpart in your database table. While this solution doesn't perform the entire operation within the query, the post-processing you need to do in PHP is minimal to get what you want, and it may be worthwhile to avoid having to create a temporary table.
...将返回列表中的所有数字，而数据库表中没有对应的数字。虽然此解决方案不会在查询中执行整个操作，但您需要在 PHP 中进行的后处理很少，以获得您想要的结果，并且避免创建临时表可能是值得的。
回答by Daniel
I had a similar problem. I had a range where the auto-incrementing primary key had some missing values, so first I found how many there were:
select count(*) from node where nid > 1962.
Comparing this number against the highest value, I got the number missing. Then I ran this query:
select n2.nid from node n1 right join node n2 on n1.nid = (n2.nid - 1) where n1.nid is null and n2.nid > 1962This will find the number of non-consecutive missing records. It won't show consecutive ones, and I'm not entirely certain how to do that, beyond changing the ON clause to allow greater latitude (which would make the JOIN table substantially larger).
In any case, this gave me five results out of the total seven missing, and the other two were guaranteed to be next to at least one of the five. If you have a larger number missing, you'll probably need some other way of finding the remaining missing.
我有一个类似的问题。我有一个范围，其中自动递增的主键有一些缺失值，所以首先我发现有多少：
select count(*) from node where nid > 1962. 将这个数字与最高值进行比较，我得到了这个数字。然后我运行这个查询：
select n2.nid from node n1 right join node n2 on n1.nid = (n2.nid - 1) where n1.nid is null and n2.nid > 1962这将找到非连续丢失记录的数量。它不会显示连续的，我不完全确定如何做到这一点，除了更改 ON 子句以允许更大的自由度（这会使 JOIN 表变得更大）。无论如何，这给了我缺失的 7 个结果中的 5 个，另外两个保证至少与五个中的一个相邻。如果您丢失了更多的数字，您可能需要一些其他方法来找到剩余的遗失物。
回答by Sunny Milenov
Alnitak's (and yours) solution should work, and I can not thing about anything else which can work only in SQL language.
Alnitak 的（和你的）解决方案应该可以工作，我不能谈论任何只能在 SQL 语言中工作的东西。
But here comes the question - how do you pass the list of values? Isn't it better to handle this in the calling code - i.e. request the ID's and compare it in the colling code, which may be in a language better suited for this kind of manipulations.
但问题来了 - 你如何传递值列表？在调用代码中处理这个不是更好 - 即请求 ID 并在 colling 代码中进行比较，这可能是一种更适合这种操作的语言。
