如果希望员工只担任一个角色，则可以使用聚合：

select id_employee
from employee_roles
group by id_employee
having count(*) = 1;

如果希望员工具有0或1个角色，则需要使用 left join :

select e.id_employee, e.name
from employees e left join
     employee_roles er
     on e.id_employee = er.id_employee
group by e.id_employee, e.name
having count(er.id_employee) <= 1;

如果要对它们进行计数，请使用子查询：

select count(*)
from (select e.id_employee, e.name
      from employees e left join
           employee_roles er
           on e.id_employee = er.id_employee
      group by e.id_employee, e.name
      having count(er.id_employee) <= 1
     ) e

请尝试以下操作
第一个选项使用 left join ```
select
name
from
(
select
id_employee,
name
from employees e
left join employee_roles er
on e.id_employee = er.employee_id
group by
id_employee,
name
having count(employee_id) <= 1
) val

第二个选项usin `union all` ```
select
    name
from employees e
where not exists (
    select 
        employee_id
    from employee_roles er
    where e.id_employee = er.employee_id
)

union all

select
    name
from employee_roles er
join employees e
on e.id_employee = er.employee_id
group by 
    name
having count(employee_id) = 1

输出：

| name  |
| ----- |
| Poul  |
| Erick |
| Joy   |
| Smith |

您需要加入表，然后使用 COUNT(*) 以及 GROUP BY . 然后你可以检查一下计数是否正确 1 筛选具有多个角色的员工。

SELECT name
FROM employees AS e
JOIN employee_roles AS r ON e.id_employee = r.id_employee
GROUP BY e.id_employee
HAVING COUNT(*) = 1