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

Question

我有一个包含患者、医生、橱柜和就诊信息的数据库。

患者（id_pac、FirstName、LastName）

医生（id_med、FirstName、LastName、专业）

内阁（id_cab、姓名）

可视化（id_viz、Date_viz） , Medic_id,Patient_id, Cabinet_id)

一名医生可以在不同的日期、在不同的柜子上拜访不同的患者。

我需要在一份报告中找到（使用子查询或连接？，但不使用中间表或视图）：

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

Answer 2

我试图更好地理解这个子查询，我找到了另一种方法（我认为是一种简单的方法）。代码是：

SELECT
CONCAT(me.Nume,' ',me.Prenume) AS Medic,
x.Denumire,
MAX(x.nrviz) AS maxviz
FROM (
        SELECT DISTINCT
            v.Medic_id,
            c.Denumire,
            COUNT(v.id_viz) AS nrviz
        FROM
            vizite v
        LEFT JOIN
            cabinete c
        ON(v.Cabinet_id=c.id_cab)
        GROUP BY
            v.Medic_id,
            v.Cabinet_id
        ORDER BY
            nrviz DESC) as x
LEFT JOIN
 medici me
ON (x.Medic_id=me.id_med)
LEFT JOIN
 cabinete ca
ON (x.Denumire=ca.Denumire)
GROUP BY
Medic
ORDER BY
    Medic,  
    maxviz DESC

@ypercube：如果您发现一些差异，请告诉我，因为了解我是否忘记或逃避某些内容很重要。