22,209
社区成员
发帖
与我相关
我的任务
分享
declare @T table (col datetime)
insert into @T
select '2013-01-01 12:45:00' union all
select '2013-01-01 20:00:00' union all
select '2013-01-02 05:00:00' union all
select '2013-01-02 12:30:00' union all
select '2013-01-02 18:00:00' union all
select '2013-01-03 01:10:00' union all
select '2013-01-03 04:00:00'
select
convert(varchar(10),dateadd(hh,-2,col),120) as 日期,
sum(
case when col
between convert(varchar(11),col,120)+'02:00:00'
and convert(varchar(11),col,120)+'08:00:00' then 1 else 0 end
) as 早餐,
sum(
case when col
between convert(varchar(11),col,120)+'12:00:00'
and convert(varchar(11),col,120)+'13:00:00' then 1 else 0 end
) as 中餐,
sum(
case when
col >=convert(varchar(11),col,120)+'17:00:00'
or col <=convert(varchar(11),col,120)+'02:00:00'
then 1 else 0 end
) as 晚餐
from @T
group by convert(varchar(10),dateadd(hh,-2,col),120)
/*
日期 早餐 中餐 晚餐
---------- ----------- ----------- -----------
2013-01-01 0 1 1
2013-01-02 1 1 2
2013-01-03 1 0 0
*/
declare @T table (col datetime)
insert into @T
select '2013-01-01 12:45:00' union all
select '2013-01-01 20:00:00' union all
select '2013-01-02 05:00:00' union all
select '2013-01-02 12:30:00' union all
select '2013-01-02 18:00:00' union all
select '2013-01-03 04:00:00'
select convert(varchar(10),col,120) as 日期,
sum(
case when col
between convert(varchar(11),col,120)+'02:00:00'
and convert(varchar(11),col,120)+'08:00:00' then 1 else 0 end
) as 早餐,
sum(
case when col
between convert(varchar(11),col,120)+'12:00:00'
and convert(varchar(11),col,120)+'13:00:00' then 1 else 0 end
) as 中餐,
sum(
case when col
between convert(varchar(11),col,120)+'17:00:00'
and convert(varchar(11),col,120)+'23:59:00' then 1 else 0 end
) as 晚餐
from @T
group by convert(varchar(10),col,120)
/*
日期 早餐 中餐 晚餐
---------- ----------- ----------- -----------
2013-01-01 0 1 1
2013-01-02 1 1 1
2013-01-03 1 0 0
*/