如何计算数据库中不同时间段的行数? [英] How can I COUNT the number of rows in a database for different time periods?
问题描述
我在Sybase DB中有一个表,其中有一个列createdDateTime。
我想要做的是计算在特定但累积的时间段之间创建了多少行,例如:
7:00 - 7:15
7:00 - 7:30 80+
7:00 - 7:45
7:00 - 8:00
...
等直到我有最后一次时间组,7:00 - 18:00。
I've got a table in a Sybase DB with a column createdDateTime.
What I want to be able to do is count how many rows were created between specific but accumulating time periods, ie:
7:00 - 7:15
7:00 - 7:30
7:00 - 7:45
7:00 - 8:00
...
and so on until I have the last time group, 7:00 - 18:00.
有一种很好的方法可以在SQL中进行一次查询,并返回所有行的所有行数:
时间 已创建行
7:00 - 7:15   ; 0
7:00 - 7:30 5
7:00 - 7:45 8
7:00 - 8:00 15
... ...
Is there a nice way to make one query in SQL that will return all the rows for me with all the row counts:
Time Rows Created
7:00 - 7:15 0
7:00 - 7:30 5
7:00 - 7:45 8
7:00 - 8:00 15
... ...
我现在有一个解决方案,但它需要我运行一个参数化查询44次,以获得所有的数据。
I have a solution at the moment, but it requires me running a parameterised query 44 times to get all the data.
感谢,
推荐答案
我最近在博客中介绍了这个确切的主题,在Sybase中工作,下面是解决方案
I recently blogged about this exact topic, not sure if it works in Sybase though, here's the solution
declare @interval int
set @interval = 5
select datepart(hh, DateTimeColumn)
, datepart(mi, DateTimeColumn)/@interval*@interval
, count(*)
from thetable
group by datepart(hh, DateTimeColumn)
, datepart(mi, DateTimeColumn)/@interval*@interval
b $ b http://ebersys.blogspot。 com / 2010/12 / sql-group-datetime-by-arbitrary-time.html
这篇关于如何计算数据库中不同时间段的行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!