表A
--------------------------
ID StartTime
1 12:30
2 13:50
3 15:05
4 15:40
--------------------------
要求得到统计结果如下,count为记录条数
------------------------------------
Time Count
0(0:00--0:59) 0
1(1:00--1:59) 0
...
12(12:00--12:59) 1
13(13:00--13:59) 1
14(14:00--14:59) 0
15(15:00--15:59) 2
...
23(23:00--23:59) 0
-----------------------------------
------解决方案--------------------
create table #(ID int, StartTime varchar(20))
insert into # select 1 , '12:30 ' union all
select 2 , '13:50 ' union all
select 3 , '15:05 ' union all
select 4 , '15:40 '
select top 24 time=identity(int,0,1) into #1 from sysobjects
select cast(time as int) as time,count(id) as [count] from # right join #1 on left(#.StartTime,2)=#1.time group by time order by time