SELECTING with multiple WHERE conditions on same column(在同一列上使用多个 WHERE 条件进行选择)
问题描述
好吧,我想我可能在这里忽略了一些明显/简单的东西...但我需要编写一个查询,该查询仅返回与同一列上的多个条件匹配的记录...
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
等...在这种情况下,您会看到联系人 99 和 100 都被标记为志愿者"和已上传"...
etc... in this case you'll see both contact 99 and 100 are flagged as both "Volunteer" and "Uploaded"...
我需要能够做的是仅返回那些与通过搜索表单输入的多个条件匹配的 contactid...contactid 必须匹配所有选择的标志...在我的脑海中,SQL 应该类似于:
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:
SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'...
但是……什么也没有返回……我在这里做错了什么?
but... that returns nothing... What am I doing wrong here?
推荐答案
你可以使用 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
(假设 contact_id, flag
是唯一的).
(assuming contact_id, flag
is unique).
或者使用连接:
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.
这篇关于在同一列上使用多个 WHERE 条件进行选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:在同一列上使用多个 WHERE 条件进行选择
基础教程推荐
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01