mysql - SQL中根据两个同步数组选择数据

Question

我有一个包含 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 循环之类的东西，但无法真正查明它是否存在以及它应该如何工作。

Answer 1

这应该有效:

SELECT * FROM table WHERE (id, version) not in ((1,4), (2,5))

更新:

这是一个 fiddle :http://sqlfiddle.com/#!9/b3284b/1