if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (ID int,time datetime,work int,uid int)
insert into [TB]
select 1,'2012/2/3 0:00:00',1,1 union all
select 2,'2012/2/29 0:00:00',0,1 union all
select 3,'2012/4/15 0:00:00',0,1 union all
select 4,'2012/4/16 0:00:00',0,1 union all
select 5,'2012/4/17 0:00:00',1,1 union all
select 6,'2012/4/18 0:00:00',2,1 union all
select 45,'2012/4/1 0:00:00',1,1 union all
select 46,'2012/4/2 0:00:00',0,1 union all
select 47,'2012/4/2 0:00:00',0,2



if object_id('[TBB]') is not null drop table [TBB]
go
create table [TBB] (ID int,workname nvarchar(4),worktime int)
insert into [TBB]
select 0,'到岗',8 union all
select 1,'请假',0 union all
select 2,'出差',10

select * from [TBB]


select * from [TB]


select uid,convert(varchar(7),time,120) as time,
sum(case when TB.work =0 then 1 else 0 end) as '到岗',
sum(case when TB.work =1 then 1 else 0 end) as '请假',
sum(case when TB.work =2 then 1 else 0 end) as '出差'
from TB
left join TBB on TB.work = TBB.ID
group by uid,convert(varchar(7),time,120)


/*
1    2012-02    1    1    0
1    2012-04    3    2    1
2    2012-04    1    0    0
*/