有一表
id 起始时间 截止时间 其他字段
1 2007-05-06 02:00:00 2007-05-07 04:03:00 XXX
2 2007-05-06 11:00:00 2007-05-06 12:30:00 XXX
3 2007-05-06 12:00:00 2007-05-06 12:30:00 XXX
3 2007-05-07 12:00:00 2007-05-10 12:30:00 XXX
现在想分每天按小时 达到如下统计效果
2007-05-06的情况
小时段 count
0:00-1:00 1
1:00-2:00 1
2:00-3:00 1
3:00-4:00 1
4:00-5:00 1
5:00-6:00 1
......
11:00-12:00 2
12:00-13:00 3
......
22:00-23:00 1
23:00-24:00 1
2007-05-07的情况
小时段 count
0:00-1:00 0
1:00-2:00 0
2:00-3:00 1
3:00-4:00 1
4:00-5:00 1
5:00-6:00 0
......
11:00-12:00 0
12:00-13:00 1
......
22:00-23:00 1
23:00-24:00 1
id 起始时间 截止时间 其他字段
1 2007-05-06 02:00:00 2007-05-07 04:03:00 XXX
2 2007-05-06 11:00:00 2007-05-06 12:30:00 XXX
3 2007-05-06 12:00:00 2007-05-06 12:30:00 XXX
3 2007-05-07 12:00:00 2007-05-10 12:30:00 XXX
现在想分每天按小时 达到如下统计效果
2007-05-06的情况
小时段 count
0:00-1:00 1
1:00-2:00 1
2:00-3:00 1
3:00-4:00 1
4:00-5:00 1
5:00-6:00 1
......
11:00-12:00 2
12:00-13:00 3
......
22:00-23:00 1
23:00-24:00 1
2007-05-07的情况
小时段 count
0:00-1:00 0
1:00-2:00 0
2:00-3:00 1
3:00-4:00 1
4:00-5:00 1
5:00-6:00 0
......
11:00-12:00 0
12:00-13:00 1
......
22:00-23:00 1
23:00-24:00 1
create table visitlog(visitTime datetime)
insert visitlog select '2001-01-01 00:02:11'
insert visitlog select '2001-01-01 00:02:11'
insert visitlog select '2001-01-01 02:02:11'
insert visitlog select '2001-01-01 02:02:11'
insert visitlog select '2001-01-01 02:02:11'
insert visitlog select '2001-01-01 02:02:11'
insert visitlog select '2001-01-01 03:02:11'
insert visitlog select '2001-01-01 03:02:11'
insert visitlog select '2001-01-01 06:02:11'
insert visitlog select '2001-01-01 06:02:11'
insert visitlog select '2001-01-01 13:02:11'
go
select
case a.visitHour
when 0 then '00时-02时(00:00-01:59)'
when 1 then '02时-04时(02:00-03:59)'
when 2 then '04时-06时(04:00-05:59)'
when 3 then '06时-08时(06:00-07:59)'
when 4 then '08时-10时(08:00-09:59)'
when 5 then '10时-12时(10:00-11:59)'
when 6 then '12时-14时(12:00-13:59)'
when 7 then '14时-16时(14:00-15:59)'
when 8 then '16时-18时(16:00-17:59)'
when 9 then '18时-20时(18:00-19:59)'
when 10 then '20时-22时(20:00-21:59)'
when 11 then '22时-24时(22:00-23:59)'
end as part,count(*) as [count]
from
(select datepart(hh,visitTime)/2 as visitHour from visitLog) a
group by
a.visitHourdrop table visitlog/*part count
------------------------ -----------
00时-02时(00:00-01:59) 2
02时-04时(02:00-03:59) 6
06时-08时(06:00-07:59) 2
12时-14时(12:00-13:59) 1*/
然后就会了,关注
对不起,应该是这样:
0:00-1:00 0
我这有起止时间啊
select 0 as hh,'00:00-01:00' as 小时段
union
select 1,'01:00-02:00'
union
select 2,'02:00-03:00'
union
select 3,'03:00-04:00'
union
select 4,'04:00-05:00'
union
select 5,'05:00-06:00'
union
select 6,'06:00-07:00'
union
select 7,'07:00-08:00'
union
select 8,'08:00-09:00'
union
select 9,'09:00-10:00'
union
select 10,'10:00-11:00'
union
select 11,'11:00-12:00'
union
select 12,'12:00-13:00'
union
select 13,'13:00-14:00'
union
select 14,'14:00-15:00'
union
select 15,'15:00-16:00'
union
select 16,'16:00-17:00'
union
select 17,'17:00-18:00'
union
select 18,'18:00-19:00'
union
select 19,'19:00-20:00'
union
select 20,'20:00-21:00'
union
select 21,'21:00-22:00'
union
select 22,'22:00-23:00'
union
select 23,'23:00-24:00'
)