sql - 按小时报表查询数据

Question

我的任务是制作一份报告，该报告将显示一个条形图，其中 X 轴是类次的小时数，因此底部为 1-8。条形是每小时完成的事务数。因此，条形图很容易让您看到在第一个小时我们处理了 30 个订单，在第二个小时我们处理了 25 个，依此类推，直到类次结束。

不过，我在弄清楚如何实际创建此报告时遇到了麻烦。做这样的事情是我唯一的选择(理解这只是伪代码，不要费心评论语法问题):

create table #temp
(
  Hour int,
  Units int
)

insert into #temp
SELECT 1 as Hour, sum(Units) Units
FROM orders
WHERE DateCreated >= '6/14/2013 08:00:00' AND DateCreated < '6/14/2013 09:00:00'

insert into #temp
SELECT 2 as Hour, sum(Units) Units
FROM orders
WHERE DateCreated >= '6/14/2013 09:00:00' AND DateCreated < '6/14/2013 10:00:00'

insert into #temp
SELECT 3 as Hour, sum(Units) Units
FROM orders
WHERE DateCreated >= '6/14/2013 11:00:00' AND DateCreated < '6/14/2013 12:00:00'

.. and so on ..

select * from #temp

另外，这是在报告调用的存储过程中。

有更好的方法吗？我是否应该将一整天的数据发送到报告并在那里以某种方式处理它？任何见解将不胜感激。

Answer 1

SELECT DATEPART(hh, DateCreated) AS hour, sum(Units) Units
FROM orders
WHERE DateCreated >= '6/14/2013' AND DateCreated < '6/15/2013'
GROUP BY DATEPART(hh, DateCreated)

阅读有关 DATEPART() 的更多信息 here .

当然，您可以根据需要为天、周添加更多分组:

SELECT DATEPART(dd, DateCreated) AS day, DATEPART(hh, DateCreated) AS hour, sum(Units) Units
FROM orders
WHERE DateCreated >= '6/10/2013' AND DateCreated < '6/15/2013'
GROUP BY DATEPART(dd, DateCreated), DATEPART(hour, DateCreated)