/*
title date time
1 2009/07/09 0849
2 2009/07/09 0852
3 2009/07/09 0902
4 2009/07/09 0925
5 2009/07/09 1310
6 2009/07/09 1318
7 2009/07/09 1747
8 2009/07/09 1817
9 2009/07/09 1819
10 2009/07/09 1822
11 2009/07/09 0030
*/ 如上,以上是一段按日期和时间来记录某一项目发生时间,现在要统计当天24小时发生的次数 ,并把这些值写入一个数组 a[24,2]的a[i,0](i 1到 24),如果某一小时内没有,就设置为零即:
date time count
2009/07/09 01 0
2009/07/09 02 0
2009/07/09 03 0
..... ... ...
2009/07/09 08 2
..... ...
2009/07/09 00 ... ---不知道说清楚没有,现在分不多了,提供50分,谢谢
title date time
1 2009/07/09 0849
2 2009/07/09 0852
3 2009/07/09 0902
4 2009/07/09 0925
5 2009/07/09 1310
6 2009/07/09 1318
7 2009/07/09 1747
8 2009/07/09 1817
9 2009/07/09 1819
10 2009/07/09 1822
11 2009/07/09 0030
*/ 如上,以上是一段按日期和时间来记录某一项目发生时间,现在要统计当天24小时发生的次数 ,并把这些值写入一个数组 a[24,2]的a[i,0](i 1到 24),如果某一小时内没有,就设置为零即:
date time count
2009/07/09 01 0
2009/07/09 02 0
2009/07/09 03 0
..... ... ...
2009/07/09 08 2
..... ...
2009/07/09 00 ... ---不知道说清楚没有,现在分不多了,提供50分,谢谢
a.[date],b.[time],isnull(count(c.title),0) as [count]
from
(select distinct [date] from 表) a
cross join
(select '00' as [time] union select '01' ... union select '23') b
left join
表 c
on
a.[date]=c.[date] and b.[time]=left(c.[time],2)
group by
a.[date],b.[time]
感谢libin_ftsafe的帮忙,用你的SQL调试成功了
-- 仅参考,推荐libin_ftsafe
declare @tab table(title int identity,date datetime,t varchar(10))
insert into @tab
select '2009-07-09','0849' union all
select '2009-07-09','0852' union all
select '2009-07-09','0902' union all
select '2009-07-09','0925' union all
select '2009-07-09','1310' union all
select '2009-07-09','1318' union all
select '2009-07-09','1747' union all
select '2009-07-09','1817' union all
select '2009-07-09','1819' union all
select '2009-07-09','1822' union all
select '2009-07-09','0030'
select left(tt,10),substring(tt,12,2),count(*)
from (
select date, convert(varchar(10),date,120)+' '+left(t,2)+':'+right(t,2)+':00' tt
from @tab) b group by left(tt,10),substring(tt,12,2)