MySQL SELECT 返回没有出现在任何行中的值列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10013475/
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
SELECT that returns list of values not occurring in any row
提问by Riz
Query:
询问：
select id from users where id in (1,2,3,4,5)
If the users table contains ids 1, 2, 3, this would return 1, 2, and 3. I want a query that would return 4 and 5. In other words, I don't want the query to return any rows that exist in the table, I want to give it a list of numbers and get the values from that list that don'tappear in the table.
如果用户表包含 id 1、2、3，这将返回 1、2 和 3。我想要一个返回 4 和 5 的查询。换句话说，我不希望查询返回任何存在的行在表格中，我想给它一个数字列表，并从该列表中获取未出现在表格中的值。
(updated to clarify question following several inapplicable answers)
（更新以澄清几个不适用的答案后的问题）
采纳答案by BugFinder
Given the numbers are a fixed list. Quickest way I can think of is have a test table, populated with those numbers and do
鉴于数字是一个固定的列表。我能想到的最快方法是有一个测试表，用这些数字填充并做
untested select statement - but you will follow the princpal.
未经测试的选择语句 - 但您将遵循 princpal。
select test.number
from test
left join
users
on
test.number = users.id
where test.number <> users.id
Then you'll get back all the numbers that dont have a matching user.id and so can fill in the holes..
然后你会得到所有没有匹配 user.id 的数字，所以可以填补漏洞..
回答by Austin
If you don't want to (explicitly) use temporary tables, this will work:
如果您不想（明确）使用临时表，这将起作用：
SELECT id FROM (
(SELECT 1 AS id) UNION ALL
(SELECT 2 AS id) UNION ALL
(SELECT 3 AS id) UNION ALL
(SELECT 4 AS id) UNION ALL
(SELECT 5 AS id)
) AS list
LEFT JOIN users USING (id)
WHERE users.id IS NULL
However, it is quite ugly, quite long, and I am dubious about how it would perform if the list of IDs is long.
然而，它很丑，很长，而且我怀疑如果 ID 列表很长它会如何执行。
回答by Guto Araujo
Had the same need and built on the answer by BugFinder using a temporary table in session. This way it will automatically be destroyed after I'm done with the query, so I don't have to deal with house cleaning as I will run this type of query often.
有同样的需求，并基于 BugFinder 在会话中使用临时表的答案。这样它会在我完成查询后自动销毁，所以我不必处理房屋清洁，因为我会经常运行这种类型的查询。
Create the temporary table:
创建临时表：
CREATE TEMPORARY TABLE tmp_table (id INT UNSIGNED);
Populate tmp_table with the values you will check:
使用您将检查的值填充 tmp_table：
INSERT INTO tmp_table (id) values (1),(2),(3),(4),(5);
With the table created and populated, run the query as with any regular table:
创建并填充表后，像使用任何常规表一样运行查询：
SELECT tmp_table.id
FROM tmp_table
LEFT JOIN users u
ON tmp_table.id = u.id
WHERE u.id IS NULL;
回答by DaveyBoy
A different option is to use another table containing all possible ids and then do a select from there:
另一种选择是使用另一个包含所有可能 id 的表，然后从那里进行选择：
mysql> describe ids;
+-------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id | int(5) unsigned | NO | | 0 | |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.05 sec)
mysql> select * from ids;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
mysql> select * from users;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> select id from ids where id not in (select id from users);
+----+
| id |
+----+
| 4 |
| 5 |
+----+
2 rows in set (0.04 sec)
Added side effect - allows you to expand the result list by inserting into the ids table
添加的副作用 - 允许您通过插入 ids 表来扩展结果列表
回答by LanderTaker
Sorry, I cannot add comments, @Austin,
抱歉，我无法添加评论，@Austin，
You have my +1.
你有我的+1。
Anyway, not sure if this works on mysql, but change all that atomic selects concatenated unions for a value set, so you have something like that:
无论如何，不确定这是否适用于 mysql，但更改所有原子选择值集的连接联合，所以你有这样的东西：
SELECT id FROM (
VALUES (1), (2), (3), (4), (5)
) AS list(id)
LEFT JOIN users USING (id)
WHERE users.id IS NULL
回答by Sergey
select missing.id
from
(select ELT(@indx, 1,2,3,4,5) as id, @indx:=@indx+1
from (select @indx:=1) init,
users
where ELT(@indx, 1,2,3,4,5) is not null
) missing
left join users u using(id)
where u.id is null;
What you have in here is:
你在这里拥有的是：
ELTtogether with the variable@indxallows you to 'transpose' the list into a column.(select @indx:=1)is needed to initialize indx to 1- and
userstable in the inner select is needed so that MySQL has something to iterate on (so the size of your list cannot exceed the number of the rows in users table, if that's the case that you can use any other table that is big enough instead of inner users, again, table itself does not matter it's just to have something to iterate on, so only its size that matters). ELT(@indx, 1,2,3,4,5) is not nullcondition in the nested select is to stop iteration once you are at the index exceeding your list size.
ELT与变量一起@indx允许您将列表“转置”为一列。(select @indx:=1)需要将 indx 初始化为 1- 并且
users需要内部选择中的表，以便 MySQL 可以迭代某些内容（因此列表的大小不能超过用户表中的行数，如果是这种情况，您可以使用任何其他足够大的表来代替对于内部用户，同样，表本身并不重要，只是有一些东西可以迭代，所以只有它的大小才重要）。 ELT(@indx, 1,2,3,4,5) is not null嵌套选择中的条件是在索引超出列表大小时停止迭代。
The rest is simple - left joinand check for null.
其余的很简单 -left join并检查null.
