create table tb(workdate datetime, empID varchar(10))
insert into tb
select '2003-10-1','001'
union
select '2003-3-4','002'
union
select '2003-5-1','001'
union
select '2003-5-1','004'select empid
,[五一] = case workdate when '2003-5-1' then 1 else 0 end
,[五二] = case workdate when '2003-5-2' then 1 else 0 end
,[五三] = case workdate when '2003-5-3' then 1 else 0 end
,[国庆一] = case workdate when '2003-10-1' then 1 else 0 end
,[国庆二] = case workdate when '2003-10-2' then 1 else 0 end
,[国庆三] = case workdate when '2003-10-3' then 1 else 0 end
into #t
from tbselect empid
,[五一]= sum([五一])
,[五二] = sum([五二])
,[五三] = sum([五三])
,[国庆一] = sum([国庆一])
,[国庆二] = sum([国庆二])
,[国庆三] = sum([国庆三])
from #t
group by empiddrop table tb
drop table #t
/*结果
001 1 0 0 1 0 0
002 0 0 0 0 0 0
004 1 0 0 0 0 0
*/
比较笨的方法,而且时间还没定义变量。
希望对你有帮助
insert into tb
select '2003-10-1','001'
union
select '2003-3-4','002'
union
select '2003-5-1','001'
union
select '2003-5-1','004'select empid
,[五一] = case workdate when '2003-5-1' then 1 else 0 end
,[五二] = case workdate when '2003-5-2' then 1 else 0 end
,[五三] = case workdate when '2003-5-3' then 1 else 0 end
,[国庆一] = case workdate when '2003-10-1' then 1 else 0 end
,[国庆二] = case workdate when '2003-10-2' then 1 else 0 end
,[国庆三] = case workdate when '2003-10-3' then 1 else 0 end
into #t
from tbselect empid
,[五一]= sum([五一])
,[五二] = sum([五二])
,[五三] = sum([五三])
,[国庆一] = sum([国庆一])
,[国庆二] = sum([国庆二])
,[国庆三] = sum([国庆三])
from #t
group by empiddrop table tb
drop table #t
/*结果
001 1 0 0 1 0 0
002 0 0 0 0 0 0
004 1 0 0 0 0 0
*/
比较笨的方法,而且时间还没定义变量。
希望对你有帮助
set @s='select b.emplname,a.sendmonth,'
FETCH NEXT FROM mm into @salaryitem
WHILE @@FETCH_STATUS = 0
BEGIN
set @s=@s+ 'SUM(CASE salaryitem when '+''''+@salaryitem+'''' +' then sendmoney else 0 end ) as '+''''+@salaryitem+''''+','
FETCH NEXT FROM mm into @salaryitem
END
set @s=substring(@s,0,len(@s))+' from rs_xc_sendpay a,RS_DA_Empl b where b.emplid=a.emplid and a.sendmonth='+@Sendmonth +' GROUP BY b.emplname,a.sendmonth order by b.emplname'
exec (@s)
workdate empID onduty
2003-3-4 001 1
2003-3-4 002 0
2003-3-4 003 0
.........
2003-5-1 001 0
2003-5-1 002 1
2003-5-1 003 0
...
其中onduty是bit型,1表示在这天值班,0表示这天不值班,想得到以下统计数据,如何写?
empID [2003-3-4] [2003-5-1]
001 1 0
002 0 1
003 0 0
谢谢大家.