SQL WHERE 子句中同一列上的多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13385653/
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
Multiple conditions on the same column in the WHERE clause
提问by tempid
I have a table something like this -
我有一张像这样的桌子——
RecordID PropertyID PropertyVal
--------------------------------------------------
3215 7 john doe
3215 11 Chicago
3215 13 Business Development Analyst
3216 7 jane doe
3216 11 Chicago
3216 13 Managing Director
3217 7 mike smith
3217 11 Chicago
3217 13 Business Development Analyst
3218 7 john smith
3218 11 Seattle
3218 13 Managing Director
How do I return the names of users where PropertyID = 13 AND PropertyVal='Business Development Analyst'AND PropertyID = 11 AND PropertyVal = 'Chicago'. How do I do multiple where clauses for the same column?
如何返回PropertyID = 13 AND PropertyVal='Business Development Analyst'AND PropertyID = 11 AND PropertyVal = 'Chicago'. 如何为同一列执行多个 where 子句？
Edit: I need the result set to look like this -
编辑：我需要结果集看起来像这样 -
Name
----
John Doe
Mike Smith
采纳答案by juergen d
select PropertyVal
from your_table
where PropertyID = 7
and RecordID in
(
select RecordID
from your_table
where (PropertyID = 13 AND PropertyVal='Business Development Analyst')
or (PropertyID = 11 AND PropertyVal = 'Chicago')
group by RecordID
having count(distinct PropertyID) = 2
)
回答by Bohemian
Not sure what you want exactly. It's probably either
不确定你到底想要什么。这可能是
...
where (PropertyID = 13 AND PropertyVal='Business Development Analyst')
or (PropertyID = 11 AND PropertyVal = 'Chicago')
or
或者
...
where PropertyID in (13, 11)
and PropertyVal in ('Business Development Analyst', 'Chicago')
回答by Arief
We can go with JOIN Clause...
我们可以使用 JOIN 子句...
Ex:
前任：
SELECT LIST_OF_COLUMNS FROM TBL1 T1 JOIN TBL2 T2
ON T1.COL1=T2.COL1 AND
T1.COL2=T2.COL2 AND
T1.COL3=T2.COL3 AND
T1.COL4=T2.COL4
回答by user6883707
SELECT LIST_OF_COLUMNS FROM TBL1 T1 JOIN TBL2 T2
ON T1.COL1=T2.COL1 AND
T1.COL2=T2.COL2 AND
T1.COL3=T2.COL3 AND
T1.COL4=T2.COL4
