带有两个表的mysql count()，不要重复超过2行

Question

我有两张桌子。

1. Table: employees

​

​

1. Table: employee_roles

​

​

我想给出所有员工的名单，如果他们的employee_id不超过2或相等的话。我是说像这样的东西我需要展示：

​

​

我如何进行查询？我使用了这个查询，但它不起作用。也有条件名称，如'%‘。

SELECT COUNT(id_employee), name 
FROM employees
WHERE name LIKE '%' AND id_employee NOT IN
(SELECT employee_id
FROM employee_roles);

Answer 1

如果希望员工具有一个角色，则可以使用聚合：

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

Answer 2

尝试以下几点

使用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

第二种选择是使用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 |

Answer 3

您需要加入这些表，然后使用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