34,576
社区成员
发帖
与我相关
我的任务
分享
tableC
Id MustCompleteOfTime State
1 2013-09-01 0
2 2013-09-01 1
3 2013-09-01 0
4 2013-09-02 1
5 2013-09-04 1
6 2013-09-04 1
7 2013-09-05 0
8 2013-09-05 0
9 2013-09-09 1
10 2013-09-09 1
11 2013-09-20 1
12 2013-09-21 0
13 2013-09-24 1
14 2013-09-24 1
15 2013-09-27 0
create table #tableC(Id int,MustCompleteOfTime datetime, State int)
insert into #tableC
select 1,'2013-09-01',0
union all select 2,'2013-09-01',1
union all select 3,'2013-09-01',0
union all select 4,'2013-09-02',1
union all select 5,'2013-09-04',1
union all select 6,'2013-09-04',1
union all select 7,'2013-09-05',0
union all select 8,'2013-09-05',0
union all select 9,'2013-09-09',1
union all select 10,'2013-09-09',1
union all select 11,'2013-09-20',1
union all select 12,'2013-09-21',0
union all select 13,'2013-09-24',1
union all select 14,'2013-09-24',1
union all select 15,'2013-09-27',0
select MustCompleteOfTime as [Day] ,case when [count]=total then '已完成' else '未完成' end Result
from
(
select CONVERT(varchar(10),MustCompleteOfTime,120) as MustCompleteOfTime,COUNT(*) as [count],SUM(State) as total
from #tableC
group by CONVERT(varchar(10),MustCompleteOfTime,120)
)t
/*
Day Result
-------------------------
2013-09-01 未完成
2013-09-02 已完成
2013-09-04 已完成
2013-09-05 未完成
2013-09-09 已完成
2013-09-20 已完成
2013-09-21 未完成
2013-09-24 已完成
2013-09-27 未完成
*/