不在表中的列表中的 ID [英] ID from list that is not in a table

问题描述

我有一个带有主键 id 的表，我们称之为员工表，它包含以下 ID:1,2,3,5,7

I have a table with a primary key id let's call it staff table and it contains the following ids: 1,2,3,5,7

而且我还有一个以逗号分隔的员工 ID 列表，例如1,2,3,4,5,6

And I also have a list of staff ids separated by comma, e.g. 1,2,3,4,5,6

如何编写一个 SQL (mysql) 来检查列表中哪些 id 不在表中(例如 4 和 6)

How do I write an SQL (mysql) that can check which ids from the list that are not in the table (e.g. 4 and 6)

我试着四处搜索，大多数结果总是在 NOT IN 附近，但这不是我想要的

I tried to search around and most of the results always around NOT IN but this is not what I want as

select * from staffs where id not in (1,2,3,4,5,6)

会给我 7 这不是我想要的，我想要 4 和 6

will return me 7 which is NOT what I want, I want 4 and 6

我也不赞成创建任何临时表

Also I'm not in favor of creating any temporary tables

谢谢

推荐答案

这行得通吗?

SELECT
   listOfIds.id
FROM
    (      SELECT 1 AS id
     UNION SELECT 2
     UNION SELECT 3
     UNION SELECT 4
     UNION SELECT 5
     UNION SELECT 6) listOfIds
LEFT OUTER JOIN staff
ON listOfIds.id = staff.id
WHERE staff.id IS NULL

试试这个 SQL Fiddle 中的例子.

这篇关于不在表中的列表中的 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！