时间段查询

[c48111926]

姓名    时间
王      2011-4-20 21:00:00
王      2011-3-20 12:00:00
陈      2011-4-20 01:00:00
陈      2011-4-20 05:00:00
我想得出
姓名   昼间   夜间
王     1       1
陈     0       2


时间节点是，昼间5:01-20:59       夜间21:00-5:00

[快溜]

select 姓名,昼间=sum(case when datepart(hh,时间)>5 and datepart(hh,时间)<21 then 1 else 0 end),
夜间=sum(case when datepart(hh,时间)<5 or datepart(hh,时间)>21 then 1 else 0 end)
from tb group by 姓名

[--小F--]

select
  姓名,
  sum(case when datepart(hh,时间) between 5 and 21 then 1 else 0 end) as 昼间,
  sum(case when datepart(hh,时间) between 21 and 24 and datepart(hh,dateadd(dd,1,时间)  between 1 and 5 then 1 else 0 end) as 夜间
from
  tb
group by
  姓名

[lddlq0810]

select
  姓名,
  sum(case when datepart(hh,时间) between 5 and 21 then 1 else 0 end) as 昼间,
  sum(case when datepart(hh,时间) NOT between 5 and 21 then 1 else 0 end) as 夜间
from tb
group by 姓名

[快溜]

用between就是5 and 20了

select 姓名,昼间=sum(case when datepart(hh,时间)>4 and datepart(hh,时间)<21 then 1 else 0 end),
夜间=sum(case when datepart(hh,时间)<5 or datepart(hh,时间)>20 then 1 else 0 end)
from tb group by 姓名

[javatemptation]

use tempdb;
/*
create table tb
(
	姓名 nvarchar(10) not null,
	时间 datetime not null
);
insert into tb(姓名,时间)
values
('王','2011-4-20 21:00:00'),
('王','2011-3-20 12:00:00'),
('陈','2011-4-20 01:00:00'),
('陈','2011-4-20 05:00:00');
*/
select tb.姓名,
COUNT(
	case 
		when datepart(HH,tb.时间) > 5 
		and datepart(HH,tb.时间) < 21 
		then 1 
	end) as [昼间],
COUNT(
	case when (datepart(HH,tb.时间) >= 21 
				and datepart(HH,tb.时间) <= 24) 
			OR (datepart(HH,tb.时间) >=0 
				and datepart(HH,tb.时间) <=5) 
		then 1 
	end) as [夜间]
from tb
group by tb.姓名;