php - 如何在查询中按年龄分组显示空数据？

Question

我有两张 table ，表 mspencaker 和 mspengalaman。我进行了一个查询，根据年龄和性别范围显示用户数量。到目前为止，查询运行顺利，但是当我更改查询日期时，查询结果将为空。如何显示数据库中的所有现有数据，即使数据为空，或者将空数据替换为 0。

这是我的 table

mspencaker
----------------
+ IDPencaker   +  --> ID OF table mspencaker(Primary key)
+ TglLahir     +  --> Date of birth from user
+ JenisKelamin +  --> gender of user
+ RegisterDate +  --> the date of registration user, and i use this field to 
----------------      filter data

mspengalaman
-------------------
+ IDpengalaman    +  --> ID OF table mspengalaman(Primary key)
+ IDPencaker      +  --> FOREIGN KEY of mspencaker
+ StatusPekerjaan +  --> i use this field to filter data
-------------------  

这是我的查询

SELECT COUNT(COALESCE(p.IDPencaker, 0)) as total, 
CASE 
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) <= 14 THEN '0-14'
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 15 AND 19 THEN '15-19' 
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 20 AND 29 THEN '20-29' 
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 30 AND 44 THEN '30-44' 
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 45 AND 54 THEN '45-54' 
 WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) >= 55 THEN '55' 
END AS age, 
SUM(COALESCE(p.JenisKelamin = 0, 0)) as laki, 
SUM(COALESCE(p.JenisKelamin = 1, 0) ) as cewe 
   FROM mspencaker p LEFT JOIN mspengalaman g 
   ON g.IDPencaker = p.IDPencaker AND g.StatusPekerjaan = 0 
   WHERE p.RegisterDate BETWEEN '2019-01-01' AND '2019-07-01' 
   GROUP BY age 

查询结果如下:

    age   | laki | cewe   | Total
----------+------+--------+------
    0-14  |  3   |   4    |  7
   15-19  |  2   |   1    |  3
   20-29  |  1   |   3    |  4
   30-44  |  10  |   1    |  11
   45-54  |  4   |   6    |  10
    55    |  2   |   1    |  3

但是如果我将 WHERE p.RegisterDate BETWEEN '2019-01-01' AND '2019-07-01' 更改为 WHERE p.RegisterDate BETWEEN '2019-06-01' AND '2019-07-01'，则结果如下:

    age   | laki | cewe   | Total
----------+------+--------+------
   null   | null | null   |  null

我希望如果我更改条件，那么所有数据仍然会出现，并被赋予 0 值而不是空值。

Answer 1

由于并非所有年龄范围每次都会出现在结果中，一种解决方案是创建另一个仅包含年龄范围(全部)的结果集，LEFT JOIN 将其与包含以下内容的结果集连接起来:您的实际数据并使用 IFNULL() 为空字段显示 0 而不是 NULL。

                SELECT t1.age,IFNULL(t2.laki,0) as laki,IFNULL(t2.cewe,0) as cewe,IFNULL(t2.Total, 0) AS Total  FROM
                (
                SELECT '0-14' AS age
                UNION ALL 
                SELECT '15-19' AS age
                UNION ALL
                SELECT '20-29' AS age
                UNION ALL
                SELECT '30-44' AS age 
                UNION ALL
                SELECT '45-54' AS age  
                UNION ALL
                SELECT '55' AS age 
                ) t1 
                LEFT JOIN 
                (
                SELECT COUNT(COALESCE(p.IDPencaker, 0)) as total, 
                CASE 
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) <= 14 THEN '0-14'
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 15 AND 19 THEN '15-19' 
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 20 AND 29 THEN '20-29' 
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 30 AND 44 THEN '30-44' 
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) BETWEEN 45 AND 54 THEN '45-54' 
                WHEN TIMESTAMPDIFF(YEAR, p.TglLahir, CURDATE()) >= 55 THEN '55' 
                END AS age, 
                SUM(COALESCE(p.JenisKelamin = 0, 0)) as laki, 
                SUM(COALESCE(p.JenisKelamin = 1, 0) ) as cewe 
                FROM mspencaker p LEFT JOIN mspengalaman g 
                ON g.IDPencaker = p.IDPencaker AND g.StatusPekerjaan = 0 
                WHERE p.RegisterDate BETWEEN '2019-01-01' AND '2019-07-01' 
                GROUP BY age
                )  t2 
                ON t1.age=t2.age