通过集团通过分组时间时，应返回0。 这个怎么做？(Group by should return

我有类似下面这样的collcation：

http://sqlfiddle.com/#!2/fecbc/3

我的问题是我想，当我使用group by HOUR(date)小时像2 ， 3和4显示，但与0 。

其实他们消失的结果。

我该如何进行呢？

什么是简单的方式做到这一点？

有没有一种方法，以避免24个IFS报表？

我们能否避免创建另一个表？

Answer 1:

您将需要一个包含24小时表，然后JOIN它。 如果你不能够创建一个表，那么你可以在你的查询中创建它。 看到这个演示 。

SELECT a.hr, IFNULL(b.hourCount, 0) hourCount
FROM 
  (SELECT 1 AS hr
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
  UNION ALL
  SELECT 4
  UNION ALL
  SELECT 5
  UNION ALL
  SELECT 6
  UNION ALL
  SELECT 7
  UNION ALL
  SELECT 8
  UNION ALL
  SELECT 9
  UNION ALL
  SELECT 10
  UNION ALL
  SELECT 11
  UNION ALL
  SELECT 12
  UNION ALL
  SELECT 13
  UNION ALL
  SELECT 14
  UNION ALL
  SELECT 15
  UNION ALL
  SELECT 16
  UNION ALL
  SELECT 17
  UNION ALL
  SELECT 18
  UNION ALL
  SELECT 19
  UNION ALL
  SELECT 20
  UNION ALL
  SELECT 21
  UNION ALL
  SELECT 22
  UNION ALL
  SELECT 23
  UNION ALL
  SELECT 0) a
LEFT JOIN
  (SELECT COUNT(id) AS hourCount, HOUR(date) AS hr
   FROM `testing`
   GROUP BY HOUR(date)) b ON b.hr = a.hr

Answer 2:

如果你不希望创建这样的表，你可以使用枚举从0到23小时的子查询：

SELECT
  v_hours.hr as hour,
  COUNT(id) AS hourCount
FROM
  (select 0 as hr union all
   select 1 as hr union all
   select 2 as hr union all
   select 3 as hr union all
   select 4 as hr union all
   select 5 as hr union all
   select 6 as hr union all
   select 7 as hr union all
   select 8 as hr union all
   select 9 as hr union all
   select 10 as hr union all
   select 11 as hr union all
   select 12 as hr union all
   select 13 as hr union all
   select 14 as hr union all
   select 15 as hr union all
   select 16 as hr union all
   select 17 as hr union all
   select 18 as hr union all
   select 19 as hr union all
   select 20 as hr union all
   select 21 as hr union all
   select 22 as hr union all
   select 23 as hr  
) v_hours
LEFT JOIN testing 
  ON HOUR(date) = v_hours.hr
GROUP BY
  v_hours.hr
ORDER BY v_hours.hr

文章来源: Group by should return 0 when grouping by hours. How to do this?