22,301
社区成员




姓名 时间
王 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
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.姓名;
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 姓名
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 姓名
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,时间)>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 姓名