Mysql:按小时分组，无数据则为0

Question

我有以下查询:

SELECT count(*) as 'totalCalls', HOUR(`end`) as 'Hour'
FROM callsDataTable 
WHERE company IN (
    SELECT number 
    FROM products 
    WHERE products.id IN (@_PRODUCTS)) 
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH 
 group by HOUR(`end`) 

以上查询仅返回调用电话的时间:

totalCalls  Hour
    2       0
    1       2
    4       7
   98       8
  325       9
  629      10
  824      13
  665      15
  678      16
  665      17
  606      18     
   89      22
    5      23

所需的输出应该是所有时间，没有调用的地方应该是那个小时的 0 个调用，如下所示:

totalCalls  Hour
    0       0
    0       1
    1       2
    0       3
    0       4
    0       5
    0       6
    4       7
   98       8
  325       9
  629      10
    0      11
    0      12
  824      13
    0      14
  665      15
  678      16
  665      17
  606      18
    0      19
    0      20
    0      21
   89      22
    5      23

Answer 1

您需要一个Hour 表，然后对Hour_table 执行left Outer Join。

这将确保返回所有小时。如果 callsDataTable 中不存在 hour，则计数将为 0。

营业时间表

create table hours_table (hours int);

insert into hours_table values(0);
insert into hours_table values(1);
 ...
insert into hours_table values(23);

查询:

SELECT count(HOUR(`end`)) as 'totalCalls', HT.Hours as 'Hour'
FROM Hours_table HT left Outer join callsDataTable CD
on HT.Hours = HOUR(`end`)
WHERE company IN (
    SELECT number 
    FROM products 
    WHERE products.id IN (@_PRODUCTS)) 
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH 
 group by HT.Hours