我有一个带有 id 和版本号(以及其他几个字段(的表,我想查找所有与我知道的 id 和版本不匹配的行。
我尝试过这样的事情,但它不是我想要的。
SELECT * FROM table WHERE id NOT IN (1,2,3) AND version NOT in (4,5,6);
上面的查询似乎是这样工作的:
SELECT * FROM table WHERE id != 1 AND version != 4;
SELECT * FROM table WHERE id != 1 AND version != 5;
SELECT * FROM table WHERE id != 1 AND version != 6;
SELECT * FROM table WHERE id != 2 AND version != 4;
SELECT * FROM table WHERE id != 2 AND version != 5;
SELECT * FROM table WHERE id != 2 AND version != 6;
SELECT * FROM table WHERE id != 3 AND version != 4;
SELECT * FROM table WHERE id != 3 AND version != 5;
SELECT * FROM table WHERE id != 3 AND version != 6;
我真正想要的是对两个列表使用相同的数组键,如下所示:
SELECT * FROM table WHERE id != 1 AND version != 4;
SELECT * FROM table WHERE id != 2 AND version != 5;
SELECT * FROM table WHERE id != 3 AND version != 6;
因此,此数据集仅返回第二行。
id | version
------------
1 | 4
2 | 7
3 | 6
我试图在SQL文档中搜索类似for循环的东西,但无法真正找出它是否存在以及它应该如何工作。
这应该有效:
SELECT * FROM table WHERE (id, version) not in ((1,4), (2,5))
更新:
这里有一个小提琴:http://sqlfiddle.com/#!9/b3284b/1