mysql - 从员工错误中选择 employee_name, count(dependent) as dependents

Question

我有这两个表“员工表，从属表”

员工表有“ID, employee_id, employee_name”

从属表有“ID, employee_id, dependent_name, relationship”

员工表的值为("1, 123, Vincent")

从属表的值为

("1, 123, Angel, daughter"),("2, 123, Mary, daughter")

我将如何显示 employee_id 和 employee_name 及其家属人数？

喜欢选择 employee_id, employee_name , count(dependent) as dependents ???

Answer 1

试试这个查询:

 select e.employee_id,e.employee_name,count(d.employee_id) as dependents
     from department d
     inner join employee e on (e.employee_id=d.employee_id) 
     group by d.employee_id;

Sql Fiddle Example