多列的 SQL“IN”语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24733328/
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
SQL "IN" statement for multiple columns
提问by user3726374
I would like to filter Name,X combinations for which is never X=Y Let's assume the following table:
我想过滤从不 X=Y 的 Name,X 组合让我们假设下表：
*Name* *X* *Y*
A 2 1
A 2 2 <--- fulfills requirement for Name=A, X=2
A 10 1
A 10 2
B 3 1
B 3 3 <--- fulfills requirement for Name=B, X=3
B 1 1 <--- fulfills requirement for Name=B, X=1
B 1 3
So I would like to return the combination Name=A, X=10 for which X=Y is never true. This was my approach (which is syntactically incorrect)
所以我想返回组合 Name=A, X=10 其中 X=Y 永远不会为真。这是我的方法（在语法上不正确）
SELECT *
FROM TABLE
WHERE NAME
, X NOT IN (SELECT DISTINCT NAME
, X
FROM TABLE
WHERE X=Y)
My problem is the where statement which cannot handle multiple columns. Does anyone know how to do this?
我的问题是 where 语句无法处理多列。有谁知道如何做到这一点？
回答by a_horse_with_no_name
Just put the columns into parentheses
只需将列放入括号中
SELECT *
FROM TABLE
WHERE (NAME, X) NOT IN (SELECT NAME, X
FROM TABLE WHERE X=Y);
The above is ANSI standard SQL but not all DBMS support this syntax though.
以上是 ANSI 标准 SQL，但并非所有 DBMS 都支持此语法。
A distinctis not necessary for a sub-query for INor NOT IN.
distinct对于INor的子查询，A不是必需的NOT IN。
However NOT EXISTSwith a co-related sub-query is very often faster that an NOT INcondition.
但是NOT EXISTS，使用相关子查询通常比NOT IN条件更快。
回答by Fabio
I think you can use two condition to achieve this
我认为你可以使用两个条件来实现这一点
SELECT *
FROM TABLE
WHERE NAME NOT IN(
SELECT a.NAME FROM TABLE a WHERE a.X=a.Y
) AND X NOT IN (
SELECT b.X FROM TABLE b WHERE b.X=b.Y
)
回答by Daniel E.
SELECT *
FROM TABLE T
WHERE NOT EXISTS (SELECT NAME
,X
FROM TABLE t2
WHERE t1.Name=t2.Name
AND t1.X=t2.Y)
This will check if there is such a record
这将检查是否有这样的记录
回答by asakura89
I use this on SQL Server
我在 SQL Server 上使用它
SELECT *
FROM TABLE
WHERE (SELECT NAME + ';' + X)
NOT IN (SELECT NAME + ';' + X
FROM TABLE WHERE X = Y);
