按小时分组时应该返回0。这个怎么做？ [英] Group by should return 0 when grouping by hours. How to do this?

问题描述

我有一个类似于这个的collcation：

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

我的问题是当我使用 group by HOUR（date） hours like 2 ， 3 和 4 出现，但是 0 。

其实它们消失了结果。

我该如何执行此操作？

执行此操作的简单方法是什么？

有没有办法避免24个ifs语句？

我们可以避免创建另一个表吗？

解决方案

您需要一个包含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（日期））b ON b.hr = a.hr 
  

I have a collcation similar to this one:

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

My problem is I would like when I use group by HOUR(date) hours like 2, 3, and 4 appears but with 0.

Actually they disappear of the result.

How can I perform this?

What is the simply way to do this?

Is there a way to avoid 24 ifs statements?

Can we avoid to create another table?

解决方案

You will need a table that contains 24 hours, then JOIN to it. If you're not able to create a table, then you can create it inside your query. See this demo.

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

这篇关于按小时分组时应该返回0。这个怎么做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！