用于匹配同一列中的多个值的SQL查询

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。这个怎么做？？

5 个解决方案

#1

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中的关系划分意味着：查找在employees表中为所有现有指定创建记录的员工。或者在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

#2

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

#3

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';

#4

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.

我知道这可能没有优化，但我发现这更容易理解和修改。

#5

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

#1