MySQL 在同一列上使用多个 WHERE 条件进行选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4047484/
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
SELECTING with multiple WHERE conditions on same column
提问by RyanNehring
Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column...
好的，我想我可能在这里忽略了一些明显/简单的东西......但我需要编写一个查询，该查询仅返回与同一列上的多个条件匹配的记录......
My table is a very simple linking setup for applying flags to a user ...
我的表是一个非常简单的链接设置，用于将标志应用于用户......
ID contactid flag flag_type
-----------------------------------
118 99 Volunteer 1
119 99 Uploaded 2
120 100 Via Import 3
121 100 Volunteer 1
122 100 Uploaded 2
etc... in this case you'll see both contact 99 and 100 are flagged as both "Volunteer" and "Uploaded"...
等等...在这种情况下，您会看到联系人 99 和 100 都被标记为“志愿者”和“已上传”...
What I need to be able to do is return those contactid's ONLY that match multiple criteria entered via a search form...the contactid's have to match ALL chosen flags... in my head the SQL should look something like:
我需要能够做的是仅返回那些与通过搜索表单输入的多个条件匹配的 contactid ……contactid 必须匹配所有选定的标志……在我的脑海中，SQL 应该类似于：
SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'...
but... that returns nothing... What am I doing wrong here?
但是......这没有任何回报......我在这里做错了什么？
回答by Mark Byers
You can either use GROUP BYand HAVING COUNT(*) = _:
您可以使用GROUP BY和HAVING COUNT(*) = _：
SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list
(assuming contact_id, flagis unique).
（假设contact_id, flag是唯一的）。
Or use joins:
或者使用连接：
SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'
If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.
如果标志列表很长并且有很多匹配项，第一个可能会更快。如果标志列表很短并且匹配项很少，您可能会发现第二个更快。如果性能是一个问题，请尝试对您的数据进行测试，看看哪个效果最好。
回答by OMG Ponies
Use:
用：
SELECT t.contactid
FROM YOUR_TABLE t
WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
HAVING COUNT(DISTINCT t.flag) = 2
The key thing is that the counting of t.flagneeds to equal the number of arguments in the INclause.
关键是计数t.flag需要等于IN子句中的参数数量。
The use of COUNT(DISTINCT t.flag)is in case there isn't a unique constraint on the combination of contactid and flag -- if there's no chance of duplicates you can omit the DISTINCT from the query:
采用COUNT(DISTINCT t.flag)的情况下，没有对的ContactID和标志的组合，唯一约束-如果没有重复的机会，你可以从查询省略DISTINCT：
SELECT t.contactid
FROM YOUR_TABLE t
WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
HAVING COUNT(t.flag) = 2
回答by wtct
Consider using INTERSECT like this:
考虑像这样使用 INTERSECT：
SELECT contactid WHERE flag = 'Volunteer'
INTERSECT
SELECT contactid WHERE flag = 'Uploaded'
I think it it the most logistic solution.
我认为这是最物流的解决方案。
回答by Orbit
can't really see your table, but flag cannot be both 'Volunteer' and 'Uploaded'. If you have multiple values in a column, you can use
无法真正看到您的表，但标志不能同时是“志愿者”和“已上传”。如果一列中有多个值，则可以使用
WHERE flag LIKE "%Volunteer%" AND flag LIKE "%UPLOADED%"
not really applicable seeing the formatted table.
看到格式化的表格并不真正适用。
回答by Pankaj Singh
Try to use this alternate query:
尝试使用此备用查询：
SELECT A.CONTACTID
FROM (SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'VOLUNTEER')A ,
(SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'UPLOADED') B WHERE A.CONTACTID = B.CONTACTID;
回答by vivek
SELECT contactid, Count(*)
FROM <YOUR_TABLE> WHERE flag in ('Volunteer','Uploaded')
GROUP BY contactid
HAVING count(*)>1;
回答by user981298
Change AND to OR. Simple mistake. Think of it like plain English, I want to select anything with that equals this or that.
将 AND 更改为 OR。简单的错误。把它想象成简单的英语，我想选择任何等于这个或那个的东西。
回答by Aitha Tarun
Use this: For example:
使用这个： 例如：
select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1001
union
select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1002
回答by talha
ANDwill return you an answer only when both volunteerand uploadedare present in your column. Otherwise it will return nullvalue...
AND只有当volunteer和uploaded都出现在您的列中时，才会返回答案。否则它将返回null值...
try using ORin your statement ...
尝试OR在您的声明中使用...
SELECT contactid WHERE flag = 'Volunteer' OR flag = 'Uploaded'
回答by Louie Aguilar
your code :
你的代码：
SELECT contactid
WHERE flag = 'Volunteer' AND flag = 'Uploaded' [...]
will not work, for you did not declare the table name. the execution will return an error message.
不会工作，因为你没有声明表名。执行将返回错误消息。
and if you want both search query to display, your code should look something like this.
如果您希望同时显示两个搜索查询，您的代码应该如下所示。
SELECT * FROM (your_table_name) WHERE flag = 'Volunteer' OR flag = 'Uploaded';
and not like this because it will always return false SELECT * FROM (your_table_name) WHERE flag = 'Volunteer' AND flag = 'Uploaded';
而不是这样，因为它总是会返回 false SELECT * FROM (your_table_name) WHERE flag = 'Volunteer' AND flag = 'Uploaded';
you can also do this
你也可以这样做
SELECT * FROM (your_table_name)
WHERE flag = 'Volunteer' OR flag = 'Uploaded'
ORDER BY contactid, flag asc;
(asc for ascending order, you can also change it to desc if you want it to display in descending order)
（asc为升序，如果你想按降序显示，也可以改为desc）
