mysql - 员工参与所有服务

Question

我有 table

Employees
id
name 

Services
id
name

EmployeesServices
id_employee
id_service

我知道如果我做

select * from Employees inner join EmployeesServices on Employees.id = EmployessServices.Employee_id

编辑:EmployeeServices 有许多Services 和Employees。我需要知道我的 Employee 是否存在每个 Services

中至少有一个 EmployeeServices

很抱歉让您困惑，但这看起来很难

Answer 1

对 EmployeesServices 中每个 id_employee 的不同 id_service 进行计数:

SELECT
  id_employee,
  COUNT(DISTINCT id_service) AS service_count
FROM
  EmployeesServices
GROUP BY
  id_employee
;

现在，您需要过滤 service_count 值。由于该表达式包含聚合函数，因此您需要使用 HAVING 子句，因为它用于过滤行组(而不是单个行，后者的作用是过滤单个行) WHERE 用于)。

据我了解，条件应该是“每个员工的不同服务数量与服务表中的服务数量相同”。那么，我们开始吧:

SELECT
  id_employee,
  COUNT(DISTINCT id_service) AS service_count
FROM
  EmployeesServices
GROUP BY
  id_employee
<b>HAVING
  COUNT(DISTINCT id_service) = (SELECT COUNT(*) FROM Services)</b>
;

现在，这只会为您提供符合条件的员工 ID。使用查询作为派生表并将其连接到 Employee 以访问员工的详细信息:

<b>SELECT
  e.*,
  es.service_count
FROM
  Employee e
INNER JOIN (</b>
  SELECT
    id_employee,
    COUNT(DISTINCT id_service) AS service_count
  FROM
    EmployeesServices
  GROUP BY
    id_employee
  HAVING
    COUNT(DISTINCT id_service) = (SELECT COUNT(*) FROM Services)
<b>) es
ON
  e.id = es.id_employee</b>
;

请注意，如果 EmployeeServices 不接受每个员工的重复服务，您可以将 COUNT(DISTINCT id_service) 替换为 COUNT(*) .