逐月对比的交叉表处理
<p>将月份做为字段名,并且反映逐月增幅的交叉表处理</p><p><br />/*--原帖地址:<br /><a href="http://community.csdn.net/Expert/topic/3841/3841808.xml?temp=.4308588">http://community.csdn.net/Expert/topic/3841/3841808.xml?temp=.4308588</a><br />--*/</p><p>--测试数据<br />create table tb(year int,month int,No varchar(10),Name varchar(10),部门 varchar(10),工资 int)<br />insert tb select 2004,10,'A001','AAA','DDD',1000<br />union all select 2004,10,'B001','BBB','DDD',800<br />union all select 2004,11,'A001','AAA','DDD',1100<br />union all select 2004,11,'B001','BBB','DDD',1000<br />union all select 2004,12,'A001','AAA','DDD',1200<br />union all select 2004,12,'B001','BBB','DDD',1050</p><p>/*--处理要求</p><p>将月份做为字段,并且反应逐月的工资增幅,例如上面的数据要求结果如下,注意最小的年月是没有增幅的</p><p>No Name 部门 2004_10 2004_11 2004_11增幅 2004_12 2004_12增幅 <br />------ ------ ------ --------- -------- ----------- --------- ------------<br />A001 AAA DDD 1000 1100 10.00% 1200 9.09%<br />B001 BBB DDD 800 1000 25.00% 1050 5.00%<br />--*/</p><p>go</p><p>--查询处理<br />declare @s nvarchar(4000),@i int<br />select @s='',@i=0<br />select @s=@s+','+quotename(fd)<br /> +'=sum(case when a.year='+year<br /> +' and a.month='+month<br /> +' then a.工资 end)'<br />+case @i when 0 then ''<br /> else ','+quotename(fd+'增幅')<br /> +'=cast(cast(sum(case when a.year='+year<br /> +' and a.month='+month<br /> +' then a.工资-b.工资 end)*100.'<br /> +'/sum(case when a.year='+year<br /> +' and a.month='+month<br /> +' then b.工资 end)'<br /> +' as decimal(10,2)) as varchar)+''%'''<br />end,@i=@i+1<br />from(<br />select year=rtrim(year),month=rtrim(month),<br /> fd=rtrim(year)+'_'+rtrim(month)<br />from tb group by year,month)a <br />exec('select a.No,a.Name,a.部门<a href="mailto:'+@s+'">'+@s+'</a><br />from tb a<br />left join tb b on a.No=b.No and a.Name=b.Name and a.部门=b.部门<br /> and a.year=b.year+(b.month)/12<br /> and a.month=b.month%12+1<br />group by a.No,a.Name,a.部门')<br />go</p><p>--删除测试<br />drop table tb</p>
页:
[1]