/*--原帖地址:

--*/

--测试数据

create table tb(year int,month int,No varchar(10),Name varchar(10),部门 varchar(10),工资 int)insert tb select 2004,10,'A001','AAA','DDD',1000union all select 2004,10,'B001','BBB','DDD',800union all select 2004,11,'A001','AAA','DDD',1100union all select 2004,11,'B001','BBB','DDD',1000union all select 2004,12,'A001','AAA','DDD',1200union all select 2004,12,'B001','BBB','DDD',1050

/*--处理要求

 将月份做为字段,并且反应逐月的工资增幅,例如上面的数据要求结果如下,注意最小的年月是没有增幅的

No     Name   部门   2004_10   2004_11  2004_11增幅  2004_12  2004_12增幅 

------ ------ ------ --------- -------- ----------- --------- ------------

A001   AAA    DDD    1000      1100     10.00%      1200      9.09%

B001   BBB    DDD    800       1000     25.00%      1050      5.00%

--*/

go

--查询处理

declare @s nvarchar(4000),@i intselect @s='',@i=0select @s=@s+','+quotename(fd)  +'=sum(case when a.year='+year  +' and a.month='+month  +' then a.工资 end)' +case @i when 0 then ''  else ','+quotename(fd+'增幅')   +'=cast(cast(sum(case when a.year='+year   +' and a.month='+month   +' then a.工资-b.工资 end)*100.'   +'/sum(case when a.year='+year   +' and a.month='+month   +' then b.工资 end)'   +' as decimal(10,2)) as varchar)+''%''' end,@i=@i+1from( select year=rtrim(year),month=rtrim(month),  fd=rtrim(year)+'_'+rtrim(month) from tb group by year,month)a exec('select a.No,a.Name,a.部门from tb a left join tb b on a.No=b.No and a.Name=b.Name and a.部门=b.部门  and a.year=b.year+(b.month)/12  and a.month=b.month%12+1 group by a.No,a.Name,a.部门')go

--删除测试

drop table tb

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=320638