mysql "and"结果集中的逻辑

Question

假设我有如下数据集:

table foo

id | employeeType | employeeID
-------------------------
 1 | Developer    | 1
 2 | Developer    | 2
 3 | Developer    | 3
 4 | Manager      | 1
 5 | Manager      | 4
 6 | Manager      | 5
 7 | CEO          | 1
 8 | CEO          | 6

我想运行一个查询，返回所有 employeeids(连同 employeeTypes)，其中所有 employeeTypes 之间有一个共同的员工 id(这是“和”逻辑。只有具有所有 employeeTypes 的 employeeIDs 才会返回。employeeType = Developer 和 employeeType=Manager 和 employeeType=CEO)。对于上面的数据，示例输出将是

result table

id | employeeType | employeeID
-------------------------
 1 | Developer    | 1
 4 | Manager      | 1
 7 | CEO          | 1

当我只有两个 employeeType 时，我可以通过像这样自行加入表来做到这一点。

select * from foo as fooOne
join foo as fooTwo
on fooOne.employeeID = fooTwo.employeeID
AND
fooOne.employeeType <> fooTwo.employeeType

当“and”逻辑匹配时，该查询返回一个包含 fooTwo 值的结果集，但同样，仅适用于两种类型的员工。我的真实用例场景表明我需要能够处理可变数量的员工类型(3、4、5 等...)

对此有任何想法将不胜感激。

Answer 1

这应该返回您想要的行:

SELECT foo.*
FROM
  foo
WHERE
  employeeID IN (
    SELECT employeeID
    FROM foo
    GROUP BY employeeID
    HAVING COUNT(DISTINCT employeeType) =
      (SELECT COUNT(DISTINCT employeeType)
       FROM foo)
  )

请看fiddle here .

内部查询将返回不同员工类型的数量:

(SELECT COUNT(DISTINCT employeeType) FROM foo)

中间查询将返回所有员工类型数量最多的员工 ID:

SELECT employeeID
FROM foo
GROUP BY employeeID
HAVING COUNT(DISTINCT employeeType) =
       (SELECT COUNT(DISTINCT employeeType) FROM foo)

并且外部查询将返回整行。