mysql 结果集什么意思_结果集中的mysql“和”逻辑

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

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)

)

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

(SELECT COUNT(DISTINCT employeeType) FROM foo)

中间查询将返回具有最大雇员类型数量的所有雇员ID：

SELECT employeeID

FROM foo

GROUP BY employeeID

HAVING COUNT(DISTINCT employeeType) =

(SELECT COUNT(DISTINCT employeeType) FROM foo)

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

标签：join,conditional,logical-operators,resultset,mysql

来源： https://codeday.me/bug/20191119/2036509.html