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 姓名
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 姓名
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 姓名
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.姓名;
夜间=sum(case when datepart(hh,时间)<5 or datepart(hh,时间)>21 then 1 else 0 end)
from tb group by 姓名
姓名,
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
姓名
姓名,
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 姓名
夜间=sum(case when datepart(hh,时间)<5 or datepart(hh,时间)>20 then 1 else 0 end)
from tb group by 姓名
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.姓名;