使用 GROUP BY 创建 SELECT 语句 [英] Create a SELECT statement with a GROUP BY

问题描述

即使数据库中有几个小时的数据，我如何通过返回所有小时的值来创建带有组的选择语句?

How can I create a select statement with a group by return values for all hours even when there is data in the database for some hours?

我有以下查询:

SELECT 
    DAY(OccurredAt) AS [Day], 
    DATEPART(HOUR,OccurredAt) AS [Hour], 
    COUNT(ID) AS [Errors]
FROM 
    Database..Error WITH (NOLOCK)
WHERE 
    YEAR(OccurredAt) = 2012 
    AND MONTH(OccurredAt) = 5
GROUP BY 
    DAY(OccurredAt), DATEPART(HOUR,OccurredAt)
ORDER BY 
    DAY(OccurredAt), DATEPART(HOUR,OccurredAt)

它返回这样的数据:

Day Hour    Errors
1   1   2
1   4   2
1   6   1
1   7   1
1   9   3
1   10  1
1   11  1
1   14  19
1   15  7
1   16  234
1   17  54
1   18  17
1   19  109
1   20  27
1   22  2
2   6   2
2   7   1
2   8   2
2   9   1
2   10  44
2   11  2
2   15  1
2   16  3
2   18  2
2   19  41
2   20  108
2   21  106
2   22  36
2   23  2

我希望它返回这样的数据:

I would like it to return data like this:

Day Hour    Errors
1   0   0
1   1   2
1   2   0
1   3   0   
1   4   2
1   5   0
1   6   1
1   7   1
1   8   0
1   9   3
1   10  1
1   11  1
1   12  0
1   13  0
1   14  19
1   15  7
1   16  234
1   17  54
1   18  17
1   19  109
1   20  27
1   21  0
1   22  2
1   23  0

所以基本上我需要在查询结果中显示零错误的时间.这些将需要显示整个日期范围，在本例中为整个 2012 年 5 月.

So basically I need the hours where there are zero errors to show in the query results as well. These will need to show for the entire date range, in this case all of May 2012.

尝试了一些东西，但到目前为止没有任何运气.

Tried a few things but not had any luck so far.

推荐答案

不要使用临时表或 CTE，而是使用永久表.在几乎所有数据库中都有一个数字(或整数)表和一个日历表非常有用.然后像您这样的查询变得容易，因为在这些表上执行外部联接以填充真实"数据中不存在的缺失数字或日期很简单.当然，这是它们许多其他用途的补充.

Instead of using temporary tables or CTEs, use permanent tables. It is extremely useful in almost all databases to have a numbers (or integers) table and a calendar table. Then queries like yours become easy, because it's simple to do an outer join on those tables to fill in missing numbers or dates that are not present in the 'real' data. That's in addition to their many other uses, of course.

另一种方法是通过您的代码散布重复的 CTE 和/或不可维护的硬编码函数.

The alternative is scattering duplicate CTEs and/or unmaintainable hard-coded functions through your code.

这篇关于使用 GROUP BY 创建 SELECT 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！