MySQL 请求 COUNT 问题

Question

我想计算上个月在每个诊所就诊的患者数量，所以这是我使用的请求:

SELECT dispensaire.IdDisp, 
 COUNT(SELECT consultation.NumDossier 
       FROM consultation 
       WHERE consultation.Date IN (SELECT consultation.Date 
                                   FROM consultation 
                                   WHERE consultation.Date BETWEEN (CURRENT_DATE( ) - INTERVAL 1 MONTH) AND CURRENT_DATE( ))) 
FROM dispensaire, medecin, consultation 
WHERE dispensaire.IdDisp=medecin.IdDisp 
  AND medecin.IdDisp.consultation.IdDisp 
GROUP BY dispensaire.IdDisp

我找不到我的请求有什么问题!谢谢

Answer 1

您在下一行中有错字 - 两列之间缺少等号:

AND medecin.IdDisp.consultation.IdDisp

该行应该是:

AND medecin.IdDisp = consultation.IdDisp

我可能建议将您的查询重写为类似这样的查询，它在您的表上使用 JOIN 语法而不是 WHERE 子句中的连接:

SELECT d.IdDisp, 
 COUNT(c.NumDossier)
FROM dispensaire d
INNER JOIN medecin m
    ON d.IdDisp = m.IdDisp 
INNER JOIN consultation c
    ON m.IdDisp = c.IdDisp
WHERE c.Date BETWEEN (CURRENT_DATE( ) - INTERVAL 1 MONTH) AND CURRENT_DATE( ))
GROUP BY d.IdDisp