MySQL 用于匹配同一列中的多个值的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16729738/
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 query for matching multiple values in the same column
提问by prasanth
I have a table in MySQL as follows.
我在 MySQL 中有一个表，如下所示。
Id Designation Years Employee
1 Soft.Egr 2000-2005 A
2 Soft.Egr 2000-2005 B
3 Soft.Egr 2000-2005 C
4 Sr.Soft.Egr 2005-2010 A
5 Sr.Soft.Egr 2005-2010 B
6 Pro.Mgr 2010-2012 A
I need to get the Employees who worked as Soft.Egr and Sr.Soft.Egr and Pro.Mgr. It is not possible to use IN or Multiple ANDs in the query. How to do this??
我需要获得担任 Soft.Egr、Sr.Soft.Egr 和 Pro.Mgr 的员工。不能在查询中使用 IN 或多个 AND。这该怎么做？？
采纳答案by Lukas Eder
What you might actually be looking for is relational division, even if your exercise requirements forbid using AND(for whatever reason?). This is tricky, but possible to express correctly in SQL.
您实际上可能要寻找的是关系除法，即使您的练习要求禁止使用AND（无论出于何种原因？）。这很棘手，但可以在 SQL 中正确表达。
Relational division in prosa means: Find those employees who have a record in the employees table for all existing designations. Or in SQL:
prosa 中的关系划分意味着：查找在员工表中所有现有名称都有记录的员工。或者在 SQL 中：
SELECT DISTINCT E1.Employee FROM Employees E1
WHERE NOT EXISTS (
SELECT 1 FROM Employees E2
WHERE NOT EXISTS (
SELECT 1 FROM Employees E3
WHERE E3.Employee = E1.Employee
AND E3.Designation = E2.Designation
)
)
To see the above query in action, consider this SQLFiddle
要查看上述查询的实际效果，请考虑此SQLFiddle
A good resource explaining relational division can be found here: http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division
可以在这里找到解释关系划分的好资源：http: //www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division
回答by
One way:
单程：
select Employee
from job_history
where Designation in ('Soft.Egr','Sr.Soft.Egr','Pro.Mgr')
group by Employee
having count(distinct Designation) = 3
回答by ninesided
If you need to get additional information back about each of the roles (like the dates) then joining back to your original table for each of the additional designations is a possible solution:
如果您需要获取有关每个角色的附加信息（例如日期），那么对于每个附加名称，返回到您的原始表是一个可能的解决方案：
SELECT t.Employee, t.Designation, t.Years, t1.Designation, t1.Years, t2.Designation, t2.Years
FROM Table t
INNER JOIN t2 ON (t2.Employee = t.Employee AND t2.Designation = 'Sr.Soft.Egr')
INNER JOIN t3 ON (t3.Employee = t.Employee AND t3.Designation = 'Soft.Egr')
WHERE t.Designation = 'Pro.Mgr';
回答by Sida Zhou
Why not the following (for postgresql)?
为什么不是以下（对于 postgresql）？
SELECT employee FROM Employees WHERE Designation ='Sr.Soft.Egr'
INTERSECT
SELECT employee FROM Employees WHERE Designation ='Soft.Egr'
INTERSECT
SELECT employee FROM Employees WHERE Designation ='Pro.Mgr'
Link to SQLfiddle
链接到SQLfiddle
I know this might not optimized, but I find this much much easier to understand and modify.
我知道这可能没有优化，但我发现这更容易理解和修改。
回答by Nageswar Rao
Try this query:
试试这个查询：
SELECT DISTINCT t1.employee,
t1.designation
FROM tempEmployees t1, tempEmployees t2, tempEmployees t3
WHERE t1.employee = t2.employee AND
t2.employee = t3.employee AND
t3.employee = t1.employee AND
t1.designation != t2.designation AND
t2.designation != t3.designation AND
t3.designation != t1.designation
