27,579
社区成员
发帖
与我相关
我的任务
分享
create table #tb(id int,name varchar(10),ktime datetime)
insert into #tb(id,name,ktime)
select 1 ,'张三' ,'2013-07-01 7: 40'
union all select 1,'张三','2013-07-01 7: 42'
union all select 1,'张三','2013-07-01 11: 40'
union all select 1,'张三','2013-07-02 18: 40'
union all select 1,'张三','2013-07-02 7: 50'
union all select 1,'张三','2013-07-02 11: 50'
union all select 1,'张三','2013-07-02 18: 10'
union all select 2,'李四','2013-07-01 7: 40'
union all select 2,'李四','2013-07-01 11: 40'
union all select 2,'李四','2013-07-02 18: 40'
union all select 2,'李四','2013-07-02 7: 50'
union all select 2,'李四','2013-07-02 18: 10'
union all select 3,'王五','2013-07-01 8: 40'
union all select 3,'王五','2013-07-01 11: 40'
union all select 3,'王五','2013-07-02 17: 40'
union all select 3,'王五','2013-07-02 7: 50'
union all select 3,'王五','2013-07-02 12: 00'
select * from #tb
select name 姓名,case when 早餐>2 then 2 else 早餐 end 早餐,
case when 中餐>2 then 2 else 中餐 end 中餐,
case when 晚餐>2 then 2 else 晚餐 end 晚餐
from (
select name,
早餐=sum(case when CONVERT(varchar(8),ktime,108) between '07:00:00' and '09:0000' then 1 else 0 end),
中餐=sum(case when CONVERT(varchar(8),ktime,108) between '11:00:00' and '13:0000' then 1 else 0 end),
晚餐=sum(case when CONVERT(varchar(8),ktime,108) between '17:00:00' and '19:0000' then 1 else 0 end)
from #tb
group by name
)t
drop table #tb
/*
李四 2 1 2
王五 2 2 1
张三 2 2 2
*/