日期:2014-05-18 浏览次数:20530 次
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 */