MySQL查询问题

Question

这是我的查询:

  SELECT count(*) as total, dp.name,dp.id,dp.description, dp.avatar 
  FROM `doctors` d  
  right join departments dp on d.department_id = dp.id 
  group by d.department_id

我有表格:医生和科室。我想提取每个科室的医生总数。这个查询工作正常，它返回我所有有医生的部门，但不返回没有医生的部门。不知何故，我想显示所有部门和总数，它代表属于某个部门的医生。我怎样才能做到这一点？

这是医生表:这是部门表

Answer 1

您可以尝试一下:

SELECT 
  (SELECT count(*) FROM doctors d WHERE d.department_id = dp.id) AS total, 
  dp.* 
FROM departments AS dp

如果您想使用JOIN，请尝试以下操作:

SELECT 
  COUNT(d.department_id) AS total,
  dp.*
FROM departments AS dp
  LEFT JOIN doctors AS d ON dp.id = d.department_id
GROUP BY dp.id