数据表visitLog:
字段:
visitTime
2006-04-10 12:22:33
...
实现:
0时-2时(0:00-1:59) 1(几条记录)
2时-4时 (2:00 -3:59) 2(几条记录)
4时-6时 (4:00-6:59) 34 (几条记录)
谢谢高人了呀
字段:
visitTime
2006-04-10 12:22:33
...
实现:
0时-2时(0:00-1:59) 1(几条记录)
2时-4时 (2:00 -3:59) 2(几条记录)
4时-6时 (4:00-6:59) 34 (几条记录)
谢谢高人了呀
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 11 then '22时-24时(22:00-23:59)'
end,
count(a.*)
from
(select hour(visitTime)%2 visitHour from visitLog) a
group by
a.visitHour
create table tb(visitTime datetime)
insert into tb select '2006-04-10 12:22:33'
insert into tb select '2006-02-10 02:22:33'
insert into tb select '2006-04-10 12:22:33'
insert into tb select '2006-04-10 05:22:33'
insert into tb select '2006-04-10 03:59:33'
insert into tb select '2006-04-10 06:59:33'
insert into tb select '2006-04-10 00:59:33'select distinct [0时-2时]=(select count(1) from tb where datepart(hh,visitTime)>=0 and datepart(hh,visitTime)<2),
[2时-4时]=(select count(1) from tb where datepart(hh,visitTime)>=2 and datepart(hh,visitTime)<4),
[4时-6时]=(select count(1) from tb where datepart(hh,visitTime)>=4 and datepart(hh,visitTime)<7)
from tbdrop table tb
hour(visitTime)%2 这个只能出现 0 或者 1???
不懂
-----------------
用datepart(hour,getdate())
所有记录都是1或者0
怎么会:
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 11 then '22时-24时(22:00-23:59)'
这样,不明白了。
比如:
datediff(hh,'00:00',visitTime)>=0 and datediff(hh,'1:59',visitTime)<=0
请教。
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*/
--------------------------------------------------------------------------------------
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 11 then '22时-24时(22:00-23:59)'
end,
count(a.*)
from
(select datepart(hh,visitTime)%2 visitHour from visitLog) a
group by
a.visitHour
--------------------------------------------------------------------------------------
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 11 then '22时-24时(22:00-23:59)'
end,
count(a.*)
from
(select datepart(hh,visitTime)/2 visitHour from visitLog) a
group by
a.visitHour
create table tb(visitTime datetime)
insert into tb select '2006-04-10 12:22:33'
insert into tb select '2006-02-10 02:22:33'
insert into tb select '2006-04-10 12:22:33'
insert into tb select '2006-04-10 05:22:33'
insert into tb select '2006-04-10 03:59:33'
insert into tb select '2006-04-10 06:59:33'
insert into tb select '2006-04-10 00:59:33'Select Part,Count(1) cnt
From
(
Select
Case DatePart(hh,visitTime)
When 0 Then '0-2'
When 1 Then '0-2'
When 2 Then '2-4'
When 3 Then '2-4'
When 4 Then '4-6'
When 5 Then '4-6'
When 6 Then '6-8'
When 7 Then '6-8'
When 8 Then '8-10'
When 9 Then '8-10'
When 10 Then '10-12'
When 11 Then '10-12'
...
When 23 Then '22-24'
End Part
From tb
) a
Group By Part
就是如何当case when搜索不到值显示为0,
若为1值没有时则显示'02时-04时(02:00-03:59) 0
libin_ftsafe(子陌红尘) 帮个忙吧。谢谢
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
b.part,
(select count(*) from visitLog where datepart(hh,visitTime)/2 = b.id) as [count]
from
(select 0 as id, '00时-02时(00:00-01:59)' as part
union select 1,'02时-04时(02:00-03:59)'
union select 2 ,'04时-06时(04:00-05:59)'
union select 3 ,'06时-08时(06:00-07:59)'
union select 4,'08时-10时(08:00-09:59)'
union select 5 ,'10时-12时(10:00-11:59)'
union select 6 ,'12时-14时(12:00-13:59)'
union select 7 ,'14时-16时(14:00-15:59)'
union select 8 ,'16时-18时(16:00-17:59)'
union select 9,'18时-20时(18:00-19:59)'
union select 10 ,'20时-22时(20:00-21:59)'
union select 11 ,'22时-24时(22:00-23:59)') border by b.iddrop table visitlog/*part count
------------------------ -----------
00时-02时(00:00-01:59) 2
02时-04时(02:00-03:59) 6
04时-06时(04:00-05:59) 0
06时-08时(06:00-07:59) 2
08时-10时(08:00-09:59) 0
10时-12时(10:00-11:59) 0
12时-14时(12:00-13:59) 1
14时-16时(14:00-15:59) 0
16时-18时(16:00-17:59) 0
18时-20时(18:00-19:59) 0
20时-22时(20:00-21:59) 0
22时-24时(22:00-23:59) 0*/