时间戳

首页 > 解决方案 > SQL请求输出上月最大数

mysql - SQL请求输出上月最大数

问题描述

我的架构：

在此处输入图像描述

我有：

doctors
id name             profession 
 1 James Harden     dental 
 2 James Jones      therapist 
 3 LeBron James     cardiologist 
 4 Kobe Braynt      surgeon 
 5 Sabrina Williams nurse 
 6 Tyler Okonma     speech therapist 
 7 John Snow        pediatrician

patients 
id name         diagnostic 
 1 Mo Bamba     tooth pulling out
 2 Kaney West   astma 
 3 Post Malone  heart attack 
 4 Denzel Curry headache 
 5 Nicola Jokic stomac-ache 
 6 Dwayne Wade  AIDS 
 7 Boo Aby      headahce

visits
doctorId  patientId  visitDate
1           1         2019-03-09
2           4         2019-03-01
2           5         2019-02-26
2           6         2019-02-05
3           3         2019-02-13
4           2         2019-03-07
7           1         2019-02-14
7           7         2019-02-15

我需要打印上个月为最多患者服务的医生。

我的要求是：

select d.name, g.counter
from doctors d inner join (
  select doctorid, count(distinct patientid) counter
  from visits
  where 
    year(visitdate) = year(current_date - interval 1 month)   -- return cur. year
    and 
    month(visitdate) = month(current_date - interval 1 month) -- return prev. month
  group by doctorid
  order by counter
) g on g.doctorid = d.id
order by g.counter desc, d.name

我的要求的结果：

   name        counter
   James Jones    2
   John Snow      2
   LeBron James   1 <- wrong

我只需要像这样输出：

       name        counter
       James Jones    2
       John Snow      2

标签： mysqlsql

解决方案

以下是上个月每位医生的就诊次数：

select doctorid, count(distinct patientid) as counter
from visits v
where visitdate >= (curdate() - interval day(visitdate) - 1 day) - interval 1 month and
      visitdate < (curdate() - interval day(visitdate) - 1 day
group by doctorid;

接下来，您需要最高值。在 MySQL 8+ 中，您将使用窗口函数。这在旧版本中更难。一种方法是having子句：

select doctorid, count(distinct patientid) as counter
from visits v
where visitdate >= (curdate() - interval day(visitdate) - 1 day) - interval 1 month and
      visitdate < curdate() - interval day(visitdate) - 1 day
group by doctorid
having count(distinct patientid) = (select count(distinct v2.patientid) as cnt
                                    from visits v2
                                    where v2.visitdate >= (curdate() - interval day(v2.visitdate) - 1 day) - interval 1 month and
                                          v2.visitdate < curdate() - interval day(v2.visitdate) - 1 day)
                                    group by v2.doctorid
                                    order by cnt desc
                                    limit 1
                                   );

获取名称和其他信息只是加入其他表的问题，您似乎知道该怎么做。

推荐阅读