USE Northwind
select
OrderID,
count(case when Quantity between 20 and 30 then ProductID end) as '20-30',
count(case when Quantity between 30 and 40 then ProductID end) as '30-40',
count(case when Quantity between 40 and 50 then ProductID end) as '40-50',
count(case when Quantity between 50 and 60 then ProductID end) as '50-60',
count(case when Quantity between 60 and 70 then ProductID end) as '60-70'
from [Order Details]
group by OrderID
（二）
--测试数据
create table tb(工资年度 int,工资月份 int,编号 varchar(10),姓名 varchar(10),部门 varchar(10),基本工资 int,实发工资 int)
insert tb select 2004,11,'A001','AAA','DDD',1000,1800
union all select 2004,11,'B001','BBB','DDD',800 ,1500
union all select 2004,12,'A001','AAA','DDD',1100,2000
union all select 2004,12,'B001','BBB','DDD',1000,1600
go
--查询处理
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@i int
select @s1='',@s2='',@i=0
select @s1=@s1+','+quotename(fd+'_基本工资')
+'=sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then a.基本工资 end)'
+case @i when 0 then ''
else ','+quotename(fd+'_基本工资增幅')
+'=cast(cast(sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then a.基本工资-b.基本工资 end)*100.'
+'/sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then b.基本工资 end)'
+' as decimal(10,2)) as varchar)+''%'''
end,
@s2=@s2+','+quotename(fd+'_实发工资')
+'=sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then a.实发工资 end)'
+case @i when 0 then ''
else ','+quotename(fd+'_实发工资增幅')
+'=cast(cast(sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then a.实发工资-b.实发工资 end)*100.'
+'/sum(case when a.工资年度='+工资年度
+' and a.工资月份='+工资月份
+' then b.实发工资 end)'
+' as decimal(10,2)) as varchar)+''%'''
end,
@i=@i+1
from(
select
工资年度=rtrim(工资年度),
工资月份=rtrim(工资月份),
fd=rtrim(工资年度)+'_'+rtrim(工资月份)
from tb group by 工资年度,工资月份)a
exec('select a.编号,a.姓名,a.部门'+@s1+@s2+'
from tb a
left join tb b on a.编号=b.编号 and a.姓名=b.姓名 and a.部门=b.部门
and a.工资年度=b.工资年度+(b.工资月份)/12
and a.工资月份=b.工资月份%12+1
group by a.编号,a.姓名,a.部门')
go
--删除测试
drop table tb
/*--测试结果--*/
Wrong:
select a.编号,a. 姓名 ,a. 部门,[2004_11月_基本工资]=b.基本工资,
[2004_12月_基本工资]=a.基本工资,
[2004年12月_基本工资增幅]=cast((1-cast(a.基本工资 as decimal(8,2))/b.基本工资)*100 as varchar(10))+'%',
[2004_11月_实发工资]=b.实发工资, [2004_12月_实发工资] =a.实发工资,
[2004年12月_实发工资增幅]=cast((1-cast(a.实发工资 as decimal(8,2))/b.实发工资)*100 as varchar(10))+'%'
from (select * from tb where 工资月份=12) a left join
(select * from tb where 工资月份=11) b
on a.编号=b.编号 and a.姓名=b.姓名 and a.部门=b.部门
select a.*, (a.2004_12月_基本工资 - a.2004_11月_基本工资) '2004年12月_基本工资增幅',
(a.2004_12月_实发工资 - a.2004_11月_实发工资) '2004年12月_实发工资增幅',
(
select max(编号) as 编号,max(姓名) as 姓名,max(部门) as 部门,
'2004_11月_基本工资'=max(case when 工资年度=2004 and 工资月份=11 then 基本工资 else 0 end),
'2004_12月_基本工资'=max(case when 工资年度=2004 and 工资月份=12 then 基本工资 else 0 end),
'2004_11月_实发工资'=max(case when 工资年度=2004 and 工资月份=11 then 实发工资 else 0 end),
'2004_12月_实发工资'=max(case when 工资年度=2004 and 工资月份=12 then 实发工资 else 0 end)
from tb
group by 编号,姓名,部门 ) a