我需要获得每个内阁每个人的访问次数的医生名单......

问题描述

我需要在21-10-2009和2013年4月12日之间获得每个内阁的访问次数的医生名单。该名单必须包含所有医生，即使他们中的一些人没有访问。

这是我设法做的：



选择医生.IdDoctors，doctors.Name，doctors.Surname，

COUNT（visits.IdPacients）AS Visits，cabinets.Name，visits.DateHour

来自医生

JOIN访问USING（IdDoctors）

JOIN cabinet USING（IdCabinets）

WHERE IdDoctors = 1 AND DATE（visits.DateHour）> 21-10-2009

及日期（visits.DateHour）< 12-04-2013 GROUP BY cabinets.Name;



但我需要这样的东西：



IdDoctors访问名称

IdDoctors 1 | ---- | cabinet.Name 3 |

IdDoctors 1 | ---- | cabinet.Name 7 |

IdDoctors 2 | ---- | cabinet.Name 5 |

IdDoctors 2 | ---- | cabinet.Name 7 |

IdDoctors 2 | ---- | cabinet.Name 1 |

IdDoctors 3 | null | null |

IdDoctors 3 | null | null |

等....

IdDoctors 98 | ---- | cabinet.Name 1 |

IdDoctors 98 | ---- | cabinet.Name 3 |

I need to get the list of doctors with the number of the visits per cabinet of each one between 21-10-2009 and 12-04-2013. The list must contain all the doctors even if some of them have no visits.
This is what I managed to do:

SELECT doctors.IdDoctors, doctors.Name, doctors.Surname,
COUNT(visits.IdPacients) AS Visits, cabinets.Name, visits.DateHour
FROM doctors
JOIN visits USING (IdDoctors)
JOIN cabinets USING (IdCabinets)
WHERE IdDoctors = 1 AND DATE(visits.DateHour) > 21-10-2009
AND DATE(visits.DateHour) < 12-04-2013 GROUP BY cabinets.Name;

but I need something like this:

IdDoctors     Visits        Name
IdDoctors 1 | ---- |cabinet.Name 3|
IdDoctors 1 | ---- |cabinet.Name 7|
IdDoctors 2 | ---- |cabinet.Name 5|
IdDoctors 2 | ---- |cabinet.Name 7|
IdDoctors 2 | ---- |cabinet.Name 1|
IdDoctors 3 | null |null |
IdDoctors 3 | null |null |
etc....
IdDoctors 98| ---- |cabinet.Name 1|
IdDoctors 98| ---- |cabinet.Name 3|

推荐答案

SELECT doctors.Name, doctors.Surname,COUNT(visits.IdPacients) AS Visits, cabinets.Name
 FROM doctors right join visits on doctors.iddoctors = visits.IdDoctors
right join cabinets on visits.IdCabinets = cabinets.idcabinet
WHERE visits.DateHour  > '21-Oct-2009'
 AND visits.DateHour < '12-Apr-2013' GROUP BY cabinets.Name, doctors.Name, doctors.Surname

BUT ;您可能需要修改它以适应您拥有的表结构（我们不知道）



也使用了2009年10月21日来获取不同的国家/地区代码，但是在您的情况下，这可能会有所不同。

BUT; You may have to modify it to suit the table structures you have (unknown to us)

Also have used 21-Oct-2009 to get round different country codes, but this may be different in your situation.

这篇关于我需要获得每个内阁每个人的访问次数的医生名单......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！