假設我有一個類似以下的數據集：

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