mysql - 统计每个月的病人

Question

我正在尝试计算每个月的患者数量，我需要查看每个月数字的结果，即使它是 0。

我正在运行的查询应该在月份号 3 上给出 13，在其他月份上给出 0。但实际情况是数字 13 被划分并分配到所有 12 个月，如下所示:

这里是查询:

SELECT 
    count(patients) as patients, month

FROM 
(
    SELECT 
        count(app_id) as patients, 
        month(date_app) as month 
    FROM 
        dentist.appointment
    WHERE id_logged=1 AND year(date_app)='2016'
    GROUP BY month(date_app)

    UNION SELECT 0,1
    UNION SELECT 0,2
    UNION SELECT 0,3
    UNION SELECT 0,4
    UNION SELECT 0,5
    UNION SELECT 0,6
    UNION SELECT 0,7
    UNION SELECT 0,8
    UNION SELECT 0,9
    UNION SELECT 0,10
    UNION SELECT 0,11
    UNION SELECT 0,12
) tmp
GROUP BY month

Answer 1

试试这个查询:

SELECT 
    count(app_id) as patients, 
    t.m as month 
FROM (    
   SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
   SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
   SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS t
LEFT JOIN dentist.appointment ON t.m = month(date_app) AND
          id_logged = 1 AND year(date_app) = 2016
GROUP BY t.m

诀窍是首先创建一个包含所有 12 个月数字的内联表，然后LEFT JOIN您的原始表到内联表。

注意谓词id_logged = 1 和year(date_app) = 2016 应该放在ON LEFT JOIN 操作的子句，否则 LEFT JOIN 变为 INNER JOIN。

Demo here