我需要获得每个内阁每个人的访问次数的医生名单...... [英] I need to get the list of doctors with the number of the visits per cabinet of each one...

问题描述

我需要在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|

推荐答案

首先，你没有说明你正在使用哪个SQL，所以我假设了SQL Server。分组时，您需要包含所有未进行数学计算的字段，并且您只需要在查询的选择部分中从查询中返回所需的字段。



您可以尝试类似

First you do not state which SQL you are using so I have assumed SQL Server. When grouping you need to include all fields that do not have a mathematical calculation in and you only need the field required back from the query in the select part of the query.

You can try something like

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.

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