mysql - 归还拥有的权利

您可以帮忙吗？可能是重复的，我了解，但需要您的帮助才能解决此问题。

我的架构：



我有这些表格：

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 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
                                   );

我请求的结果：

   doctorId   counter
   7              2

我不明白，为什么它只返回一个doctorId。

我只需要这样输出：

       doctorId   counter
        7           2
        3           2

非常感谢。

最佳答案

如果您使用的是旧版本，则可以使用以下查询：

SELECT doctorid, SUM(1) as counter
FROM visits v
WHERE visitdate
    BETWEEN
        LAST_DAY(now() - INTERVAL 2 MONTH) + interval 1 DAY
    AND
        LAST_DAY(now() - INTERVAL 1 MONTH)
GROUP BY doctorId
HAVING counter = (
    SELECT count(*) as cnt
    FROM visits
    WHERE visitdate
        BETWEEN
            LAST_DAY(now() - INTERVAL 2 MONTH) + interval 1 DAY
        AND
            LAST_DAY(now() - INTERVAL 1 MONTH)
    GROUP BY doctorId
    ORDER BY cnt DESC
    LIMIT 1
) ;

关于mysql - 归还拥有的权利，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55173237/