mysql - 找出参加人数最多的医生的病人吗？

Question

我正在使用这些表：

表Adm_Med：


  Adm_ID / * ID入院
  
  Med_ID
  
  Doc_ID / * ID参加相应Adm_ID患者的医生


表入场：


  Adm_ID
  
  Pat_ID / * ID患者
  
  入场日期
  
  日期放电


表患者：


  Pat_ID
  
  Pat_Lastname
  
  Pat_Firstname
  
  生日


我想查询返回类似：


  Adm_ID | Pat_Lastname | Pat_Firstname |人数注意


但是我有两个怀疑：


  1）与我编写的脚本相比（见下文），有一个
  给出以上结果的更有效的方法？我觉得我写了很多。
  
  2）到目前为止，我刚能够获得前三列。我没有
  想法如何将Numberattentions列。我一直在尝试这样做，但是每次都会出错。


我的尝试（在这里，我逐步解释了我所做的事情，但最后是完整的查询。如果您不想阅读全部内容）：

我计算了给定Adm_ID中就诊患者的医生人数

SELECT Adm_ID, COUNT(doc_id) as numdoc
FROM Adm_Med
GROUP BY Adm_ID

然后采取最大。这些注意中

SELECT MAX(numdoc)
FROM (SELECT Adm_ID, COUNT(doc_id) as numdoc
FROM Adm_Med
GROUP BY Adm_ID) temp

与相应的 Adm_ID匹配

SELECT Adm_ID
FROM Adm_Med
GROUP BY Adm_ID
HAVING COUNT(doc_id) IN (SELECT MAX(numdoc)
                         FROM (SELECT Adm_ID, COUNT(doc_id) as numdoc
                               FROM Adm_Med
                               GROUP BY Adm_ID) temp)

在表 Admission和 Patient之间形成内部联接，通过 WHERE ... IN子句将 Admission.Adm_ID与 Adm_ID匹配，以引起医生最多的注意（这是我得到的最终脚本） ：

SELECT Adm_ID, Pat_Lastname, Pat_Firstname
FROM Admission a 
INNER JOIN Patient p
    ON a.Pat_ID=p.Pat_ID
    WHERE Adm_ID IN (SELECT Adm_ID
                    FROM Adm_Med
                    GROUP BY Adm_ID
                    HAVING COUNT(doc_id) IN 
                               (SELECT MAX(numdoc)
                                FROM (SELECT Adm_ID, COUNT(doc_id) as numdoc
                                FROM Adm_Med
                                GROUP BY Adm_ID) temp))

Answer 1

由于您之前的问题与MySQL有关，所以我猜这也是使用MySQL。

因此，令人遗憾的是，MySQL不支持通用表表达式（WITH子句）。这将允许重复使用查询来计算最大值。

因此，以下sql应该返回我认为您要查找的内容。

但是，我太罗y了。
我心中的高尔夫球手正在哭泣。因此，我很想知道是否有人可以找到一种更简洁的方法。

select q1.Adm_ID, p.Pat_Firstname, p.Pat_Lastname, q1.TotalDoctors as Numberattentions
from (
    select m.Adm_ID, a.Pat_ID, count(m.Doc_ID) as TotalDoctors
    from Admission a
    join Adm_Med m on (a.Adm_ID = m.Adm_ID)
    group by m.Adm_ID, a.Pat_ID
) q1
join (
    select Pat_ID, max(TotalDoctors) as MaxTotalDoctors
    from (
      select a.Pat_ID, count(m.Doc_ID) as TotalDoctors
      from Admission a
      join Adm_Med m on (a.Adm_ID = m.Adm_ID)
      group by m.Adm_ID, a.Pat_ID
    ) q0
    group by Pat_ID
) q2
on (q1.Pat_ID = q2.Pat_ID and q1.TotalDoctors = q2.MaxTotalDoctors)
left join Patient p on (q1.Pat_ID = p.Pat_ID);

一些测试数据：

create table Adm_Med (Adm_ID int, Med_ID int, Doc_ID int);
insert into Adm_Med (Adm_ID, Med_ID, Doc_ID) values 
(101,201,301),(101,202,302),(102,203,301),(102,204,302),(102,205,303),
(103,201,301),(103,202,302),(104,203,301),(104,204,302),(104,205,303);

create table Admission (Adm_ID int, Pat_ID int, Date_Admission date, Date_Discharge date);
insert into Admission (Adm_ID, Pat_ID, Date_Admission, Date_Discharge) values
(101,401,'2016-01-01','2016-02-01'),(102,401,'2016-03-01','2016-04-01'),
(103,402,'2016-01-01','2016-02-01'),(104,402,'2016-03-01','2016-04-01');

create table Patient (Pat_ID int, Pat_Firstname varchar(30), Pat_Lastname varchar(30), Birthdate date);
insert into Patient (Pat_ID, Pat_Firstname, Pat_Lastname, Birthdate) values 
(401,'John','Doe','1941-05-03'),(402,'Jane','Doe','1942-06-04');

对于仅一名患者，可以使用较短的SQL：

select m.Adm_ID, p.Pat_Firstname, p.Pat_Lastname, count(m.Doc_ID) as Numberattentions
from Admission a
join Adm_Med m on (a.Adm_ID = m.Adm_ID)
join Patient p on (a.Pat_ID = p.Pat_ID)
where p.Pat_ID = 402
group by m.Adm_ID, a.Pat_ID
order by Numberattentions desc
limit 1;