mysql - 使用 MAX 和 COUNT 从 3 个不同的表生成报告？

Question

我有一个包含患者、医生、内阁和访问的数据库。

患者(id_pac、名字、姓氏)

医生(id_med、名字、姓氏、专业)

机柜(id_cab，名称)

访问次数(id_viz、Date_viz、Medic_id、Patient_id、Cabinet_id)

一位医生可以在不同的日期、不同的柜子里拜访不同的病人。

我需要在一份报告中找出(使用子查询或连接？，但不是中间表或 View ):

1. 包含医生全名的一列:CONCAT(FirstName,' ',LastName) (DISTINCT),
2. 一列包含医生就诊次数最多的柜名和3.一栏是这个柜子的访问次数。

我试图弄清楚如何获得这些信息，但我所能拥有的只是他访问过的每个机柜的医生名单，以及访问总数/机柜:((

Answer 1

SELECT CONCAT(d.FirstName,' ',d.LastName) AS CompleteName
     , c.Name                             AS CabinetName  
     , dv.MaxVisits                       AS MaxVisits
FROM Doctors AS d
  LEFT JOIN
    ( SELECT Medic_id
           , MAX(NumVisits) AS MaxVisits
      FROM
        ( SELECT Medic_id
               , Cabinet_id
               , COUNT(*) AS NumVisits
          FROM Vizits AS v
          GROUP BY Medic_id
                 , Cabinet_id
        ) AS grp
      GROUP BY Medic_id
    ) AS dv
    ON dv.Medic_id = d.id_med
  LEFT JOIN
    ( SELECT Medic_id
           , Cabinet_id
           , COUNT(*) AS NumVisits
      FROM Vizits AS v
      GROUP BY Medic_id
             , Cabinet_id
    ) AS mcv
    ON  mcv.Medic_id = dv.Medic_id
    AND mcv.NumVisits = dv.MaxVisits
  LEFT JOIN Cabinets AS c
      ON c.id_cab = mcv.Cabinet_id