mysql - COUNT 多表查询，我必须在其中显示 COUNT 个结果

Question

我该如何解决这个问题？我的查询都不起作用，而且我尝试了很少。

我必须返回订购最多分析的两位医生的详细信息，包括他们订购的分析次数。这句话的最后一部分真的让我发疯。

要使用我有这两个表:

mysql> select * from DOCTORES;
+-----------+---------------------------+--------------+-----------+
| DNI_DOC   | NOMBRE_DOC                | ESPECIALIDAD | TELEFONO  |
+-----------+---------------------------+--------------+-----------+
| 22888444O | MATEO DÍAZ, RAMÓN         |            3 | 659876457 |
| 22909456Y | HERAS PRADO, ANTONIA      |            1 | 676234598 |
| 23456398F | GÓMEZ DAVID, ADRIÁN       |            1 | 646768454 |
| 25349857H | BURGOS CASA, CANDY        |            2 | 659758476 |
| 55776898K | RAMÓN CORONADO,LUIS       |            3 | 654364736 |
| 78988484B | CONRADO ALONSO, JOSE      |            2 | 645878745 |
| 88647389P | DOMÍNGUEZ GÓMEZ, MANUEL   |            1 | 623787343 |
+-----------+---------------------------+--------------+-----------+

和:

mysql> select * from PETICIONES;
+--------+------------+--------+-----------+-----------+
| ID_PET | FECHA_PET  | ID_ANA | DNI_PAC   | DNI_DOC   |
+--------+------------+--------+-----------+-----------+
|      1 | 2008-01-03 |      2 | 71515623A | 23456398F |
|      2 | 2008-05-10 |      2 | 33788976F | 55776898K |
|      3 | 2008-05-08 |      3 | 79876867X | 23456398F |
|      4 | 2008-05-11 |      4 | 44787345H | 55776898K |
|      5 | 2008-05-12 |      2 | 19887234W | 25349857H |
|      6 | 2008-05-05 |      4 | 22897576R | 55776898K |
|      7 | 2008-03-15 |      5 | 44787345H | 88647389P |
|      8 | 2008-03-19 |      1 | 71515623A | 23456398F |
|      9 | 2008-03-26 |      2 | 71515623A | 78988484B |
|     10 | 2008-03-15 |      2 | 19887234W | 88647389P |
|     11 | 2008-03-15 |      3 | 33788976F | 55776898K |
|     12 | 2008-03-26 |      2 | 44787345H | 23456398F |
+--------+------------+--------+-----------+-----------+

我试过这个:

select 
    NOMBRE_DOC 
from 
    DOCTORES 
where 
    DNI_DOC IN (select DNI_DOC 
                from PETICIONES 
                group by ID_ANA 
                order by count(*) desc 
                limit 2) 
group by 
    DNI_DOC;

还有:

SELECT 
    DNI_DOC, ID_ANA, COUNT(ID_ANA) AS count 
FROM
    TIPOS_ANALISIS     
WHERE 
    ID_ANA = (SELECT ID_ANA 
              FROM 
                  (SELECT 
                       ID_ANA, COUNT(ID_ANA) as AnaCount 
                   FROM
                       PETICIONES     
                   GROUP BY 
                       by ID_ANA     
                   ORDER BY
                       AnaCount DESC 
                   LIMIT 1) t1) 
GROUP 
    BY ID_ANA;

还有:

select a.* from DOCTORES a
    -> where a.DNI_DOC = ( SELECT b.DNI_DOC from PETICIONES b
    -> group by d.ID_ANA
    -> order by count(ID_ANA) DESC
    -> limit 2 );

当你说这是一个简单的查询时，你甚至无法想象它是多么令人沮丧......为什么它对我来说不简单是一个谜(前提是我真的不是傻瓜)。

预期的输出是这样的:

+-----------+---------------------------+--------------+-----------+
| DNI_DOC   | NOMBRE_DOC                |    ID_ANA    |  count    |
+-----------+---------------------------+--------------+-----------+
| 22888444O | MATEO DÍAZ, RAMÓN         |            3 |        6  |
+-----------+---------------------------+--------------+-----------+

Answer 1

我相信你把这个严重地复杂化了。加入 DNI_DOC 字段上的 2 个表，按医生的 ID 和姓名(以及您可能希望包含在选择列表中的任何其他详细信息)分组，并计算不同 ID_ANA 的数量。

SELECT d.DNI_DOC, d.NOMBRE_DOC, COUNT(distinct ID_ANA) AS count
FROM DOCTORES d
INNER JOIN PETICIONES t ON d.DNI_DOC=t.DNI_DOC
GROUP BY d.DNI_DOC, d.NOMBRE_DOC
ORDER BY COUNT(distinct ID_ANA) DESC
LIMIT 2

如果您需要每个医生的 peticiones 总数，请从计数中删除不同的部分。