在同一列上选择多个条件
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"...
等等……在本例中,您将看到contact 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?
但是…返回什么……我在这里做错了什么?
11 个解决方案
#1
65
You can either use GROUP BY
and HAVING COUNT(*) = _
:
您可以使用GROUP BY和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, flag
is unique).
(假设contact_id是惟一的)。
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.
如果标志的列表很长,并且有很多匹配项,那么第一个可能会更快。如果标记列表很短,并且几乎没有匹配,那么您可能会发现第二个标记更快。如果性能是一个问题,请尝试在数据上测试这两个数据,看看哪一个最有效。
更多相关文章
- 已开启GTID的情况下的binlog复制切换到GTID复制可能会遇到的问题
- hibernate(*.hbm.xml)中新添加的字段被标记为红色(找不到)的解决方法
- 可以访问Stackoverflow的自动建议标记系统吗?
- 使用.after()添加html关闭和打开标记
- 如何使用条件if()使用javascript检索带有指定值的选择标记的xml数
- 使用异步库汇编数据库中的所有标记
- adobeindesign JavaScript XML:如何以编程方式添加XML结构标记?
- 在angularjs中选择值时,html中的选择标记值会消失
- 使用angularjs添加包含事件的新标记