假设我有一个类似以下的数据集：

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

我想运行一个查询,该查询将返回所有employeeid(以及employeeTypes),其中所有employeeTypes之间都有一个通用的employee id(即“与”逻辑.只有所有employeeTypes的employeeID都将返回.employeeType= Developer和employeeType =经理,employeeType = CEO).对于上面的数据,示例输出为

result table

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

当我只有两个员工类型通过自我加入表格时,便能够做到这一点.

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

当“和”逻辑匹配时,该查询返回带有fooTwo值的结果集,但同样,仅适用于两种类型的员工.我的实际用例场景要求我需要能够处理可变数量的employeeType(3、4、5等).

任何对此的想法将不胜感激.

解决方法:

这应该返回所需的行：

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)

并且外部查询将返回整个行.