??
1.
select [14:00-16:00]=sum(case when convert(varchar(5),[Field],108)>='14:00' and convert(varchar(5),[Field],108)<'16:00' then 1 else 0 end),
[16:00-20:00]=sum(case when convert(varchar(5),[Field],108)>='16:00' and convert(varchar(5),[Field],108)<'20:00' then 1 else 0 end),
[20:00-24:00]=sum(case when convert(varchar(5),[Field],108)>='20:00' and convert(varchar(5),[Field],108)<'23:59' then 1 else 0 end)
from [Table] where [Field]='2007-11-20'2.
select count(1) from [Table] where [Field]='2007-11-20'
1.
select [14:00-16:00]=sum(case when convert(varchar(5),[Field],108)>='14:00' and convert(varchar(5),[Field],108)<'16:00' then 1 else 0 end),
[16:00-20:00]=sum(case when convert(varchar(5),[Field],108)>='16:00' and convert(varchar(5),[Field],108)<'20:00' then 1 else 0 end),
[20:00-24:00]=sum(case when convert(varchar(5),[Field],108)>='20:00' and convert(varchar(5),[Field],108)<'23:59' then 1 else 0 end)
from [Table] where [Field]='2007-11-20'2.
select count(1) from [Table] where [Field]='2007-11-20'
insert into @tb select 1,'2007-12-20 01:19:56.640'
insert into @tb select 1,'2007-12-20 02:19:56.640'
insert into @tb select 1,'2007-12-20 03:19:56.640'
insert into @tb select 1,'2007-12-20 04:19:56.640'
insert into @tb select 1,'2007-12-20 05:19:56.640'
insert into @tb select 1,'2007-12-20 06:19:56.640'
insert into @tb select 1,'2007-12-20 07:19:56.640'
insert into @tb select 1,'2007-12-20 09:19:56.640'
insert into @tb select 1,'2007-12-20 10:19:56.640'
insert into @tb select 1,'2007-12-20 11:19:56.640'
insert into @tb select 1,'2007-12-20 12:19:56.640'
insert into @tb select 1,'2007-12-20 13:19:56.640'select
case
when starttime between '2007-12-20 01:00:00.000' and '2007-12-20 04:00:00.000' then '1点到4点'
when starttime between '2007-12-20 04:00:00.000' and '2007-12-20 08:00:00.000' then '4点到8点'
when starttime between '2007-12-20 08:00:00.000' and '2007-12-20 13:00:00.000' then '8点到13点'
else '未知'
end as [时间范围],count(*) as '总量'
from @tb group by case
when starttime between '2007-12-20 01:00:00.000' and '2007-12-20 04:00:00.000' then '1点到4点'
when starttime between '2007-12-20 04:00:00.000' and '2007-12-20 08:00:00.000' then '4点到8点'
when starttime between '2007-12-20 08:00:00.000' and '2007-12-20 13:00:00.000' then '8点到13点'
else '未知'
end时间范围 总量
1点到4点 3
4点到8点 4
8点到13点 4
未知 1
我用以下语句(改后进行查询的)
select
case
when starttime between '2007-12-20 01:00:00.000' and '2007-12-20 04:00:00.000' then '1点到4点'
when starttime between '2007-12-20 04:00:00.000' and '2007-12-20 08:00:00.000' then '4点到8点'
when starttime between '2007-12-20 08:00:00.000' and '2007-12-20 13:00:00.000' then '8点到13点'
else '未知'
end as [时间范围],count(*) as '总量'
from @tb group by case
when starttime between '2007-12-20 01:00:00.000' and '2007-12-20 04:00:00.000' then '1点到4点'
when starttime between '2007-12-20 04:00:00.000' and '2007-12-20 08:00:00.000' then '4点到8点'
when starttime between '2007-12-20 08:00:00.000' and '2007-12-20 13:00:00.000' then '8点到13点'
else '未知'
end