SELECT返回不存在任何行的值的列表 [英] SELECT that returns list of values not occurring in any row

问题描述

查询:

select id from users where id in (1,2,3,4,5)

如果users表包含ID 1、2、3，则它将返回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't appear in the table.

(已更新，以在出现几个不适用的答案后澄清问题)

(updated to clarify question following several inapplicable answers)

推荐答案

鉴于数字是固定列表.我能想到的最快方法是有一张测试表，上面填入这些数字，然后执行

Given the numbers are a fixed list. Quickest way I can think of is have a test table, populated with those numbers and do

unested select语句-但您将遵循原则.

untested select statement - but you will follow the princpal.

select test.number 
from test 
left join 
    users 
on 
    test.number = users.id 
where test.number <> users.id

然后，您将获得所有没有匹配的user.id的数字，因此可以填补空缺..

Then you'll get back all the numbers that dont have a matching user.id and so can fill in the holes..

这篇关于SELECT返回不存在任何行的值的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！