如果一小时内没有找到数据，Mysql 每小时查询 group by 返回零

Question

我有一个三层mysql数据库表，其中包含客户公司的通话记录。我想按小时从数据库中获取公司组的数据。如果一小时内没有数据，则应返回零。

我正在执行此查询，但仅在数据库中有数据的情况下获得该小时的结果。

这是我的查询。

select ph_Plans.Comp_ID, Plan_Type, ph_Companies.CompanyName,
    (sum(call_length_billable)*100)/(Plan_Limit*60) as total,
    hour(calldate)
from ph_Plans
join ph_Companies
    on ph_Companies.Comp_ID = ph_Plans.Comp_ID
join cdr
    on cdr.CompanyName = ph_Companies.CompanyName
where Plan_Type='Per_Min'
    and date(calldate)='2012-10-01'
    and ph_Companies.CompanyName='"ReadySpace-EN"'
group by hour(calldate);

这是得到的结果。

+---------+-----------+-----------------+--------+----------------+
| Comp_ID | Plan_Type | CompanyName     | total  | hour(calldate) |
+---------+-----------+-----------------+--------+----------------+
|      44 | Per_Min   | "ReadySpace-EN" | 3.7467 |              1 |
|      44 | Per_Min   | "ReadySpace-EN" | 9.4933 |             18 |
|      44 | Per_Min   | "ReadySpace-EN" | 1.6600 |             20 |
|      44 | Per_Min   | "ReadySpace-EN" | 3.7333 |             21 |
|      44 | Per_Min   | "ReadySpace-EN" | 4.6067 |              2 |
|      44 | Per_Min   | "ReadySpace-EN" | 7.6533 |             23 |
+---------+-----------+-----------------+--------+----------------+

但我想要从零点到二十三点的结果。如果没有数据，那么它应该返回零。

Answer 1

在进行JOIN时，您只能JOIN在两个表上都具有与被JOINed的列相对应的数据。当您仍然想要另一个表中没有相应数据的结果时，您想要做的是LEFT JOIN。

类似这样的事情:

select ph_Plans.Comp_ID, Plan_Type, ph_Companies.CompanyName,
    if(call_length_billable IS NULL, 0, (sum(call_length_billable)*100)/(Plan_Limit*60)) as total,
    ifnull(hour(calldate), 0) hour
from ph_Plans
left join ph_Companies
    on ph_Companies.Comp_ID = ph_Plans.Comp_ID
left join cdr
    on cdr.CompanyName = ph_Companies.CompanyName
where Plan_Type='Per_Min'
    and (date(calldate)='2012-10-01' OR calldate IS NULL)
    and (ph_Companies.CompanyName='"ReadySpace-EN"' OR ph_Companies.CompanyName IS NULL)
group by hour(calldate);