select 商品编码,sum(case when 年份=2001 then 一月数量 end)/
(case when 年份=2002 then 一月数量*1.0 end)*100,
sum(case when 年份=2001 then 二月数量 end)/
(case when 年份=2002 then 二月数量*1.0 end)*100,
...
from 表
group by 商品编码
order by 商品编码
(case when 年份=2002 then 一月数量*1.0 end)*100,
sum(case when 年份=2001 then 二月数量 end)/
(case when 年份=2002 then 二月数量*1.0 end)*100,
...
from 表
group by 商品编码
order by 商品编码
(case when 年份=2002 then 一月数量 end),
sum(case when 年份=2001 then 二月数量 end)*100.0/
(case when 年份=2002 then 二月数量 end),
...
from 表
group by 商品编码
order by 商品编码
go
create table #lfy(id int,sl1 numeric,sl2 numeric,sl3 numeric,sl4 numeric,sl5 numeric,
sl6 numeric,sl7 numeric,sl8 numeric,sl9 numeric,sl10 numeric,sl11 numeric,sl12 numeric,yearid varchar(4))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'select (b.sl1-a.sl1)/a.sl1 as pren1,(b.sl2-a.sl2)/a.sl2 as pren2,(b.sl3-a.sl3)/a.sl3 as pren3,
(b.sl4-a.sl4)/a.sl4 as pren4,(b.sl5-a.sl5)/a.sl5 as pren5,(b.sl6-a.sl6)/a.sl6 as pren6,
(b.sl7-a.sl7)/a.sl7 as pren7,(b.sl8-a.sl8)/a.sl8 as pren8,(b.sl9-a.sl9)/a.sl9 as pren9,
(b.sl10-a.sl10)/a.sl10 as pren10,(b.sl11-a.sl11)/a.sl11 as pren11,(b.sl12-a.sl12)/a.sl12 as pren12
from #lfy as a inner join #lfy as b on a.id=b.id and a.yearid='2001' and b.yearid='2002'
go
create table #lfy(id int,sl1 numeric,sl2 numeric,sl3 numeric,sl4 numeric,sl5 numeric,
sl6 numeric,sl7 numeric,sl8 numeric,sl9 numeric,sl10 numeric,sl11 numeric,sl12 numeric,yearid varchar(4))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'select a.id,(b.sl1-a.sl1)/a.sl1 as pren1,(b.sl2-a.sl2)/a.sl2 as pren2,(b.sl3-a.sl3)/a.sl3 as pren3,
(b.sl4-a.sl4)/a.sl4 as pren4,(b.sl5-a.sl5)/a.sl5 as pren5,(b.sl6-a.sl6)/a.sl6 as pren6,
(b.sl7-a.sl7)/a.sl7 as pren7,(b.sl8-a.sl8)/a.sl8 as pren8,(b.sl9-a.sl9)/a.sl9 as pren9,
(b.sl10-a.sl10)/a.sl10 as pren10,(b.sl11-a.sl11)/a.sl11 as pren11,(b.sl12-a.sl12)/a.sl12 as pren12
from #lfy as a inner join #lfy as b on a.id=b.id and a.yearid='2001' and b.yearid='2002'
SELECT A.商品编码,一月增长率=(B.一月数量 - A.一月数量)/B.一月数量),二月增长率=(B.二月数量 - A.二月数量)/B.二月数量),...十二月增长率=(B.十二月数量 - A.十二月数量)/B.十二月数量)
FORM (SELECT 商品编码,一月数量=SUM(一月数量),二月数量=SUM(二月数量)...十二月数量=SUM(十二月数量) FROM YOUTABLE GROUP BY 商品编码,年份 WHERE 年份='2001') A
JOIN (SELECT 商品编码,一月数量=SUM(一月数量),二月数量=SUM(二月数量)...十二月数量=SUM(十二月数量) FROM YOUTABLE GROUP BY 商品编码,年份 WHERE 年份='2001') B
ON A.商品编码=B.商品编码
sl6 int,sl7 int,sl8 int,sl9 int,sl10 int,sl11 int,sl12 int,yearid varchar(4))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'select a.id,cast((b.sl1-a.sl1-0.0)/a.sl1 as numeric(10,2)) pren1,cast((b.sl2-a.sl2-0.0)/a.sl2 as numeric(10,2)) pren2,cast((b.sl3-a.sl3-0.0)/a.sl3 as numeric(10,2)) pren3,
cast((b.sl4-a.sl4-0.0)/a.sl4 as numeric(10,2)) pren4,cast((b.sl5-a.sl5-0.0)/a.sl5 as numeric(10,2)) pren5,cast((b.sl6-a.sl6-0.0)/a.sl6 as numeric(10,2)) pren6,
cast((b.sl7-a.sl7-0.0)/a.sl7 as numeric(10,2)) pren7,cast((b.sl8-a.sl8-0.0)/a.sl8 as numeric(10,2)) pren8,cast((b.sl9-a.sl9-0.0)/a.sl9 as numeric(10,2)) pren9,
cast((b.sl10-a.sl10-0.0)/a.sl10 as numeric(10,2)) pren10,cast((b.sl11-a.sl11-0.0)/a.sl11 as numeric(10,2)) pren11,cast((b.sl12-a.sl12-0.0)/a.sl12 as numeric(10,2)) pren12
from #lfy a , #lfy b where a.id=b.id and a.yearid=b.yearid-1
go
drop table #lfy
......
from 表
group by 商品编码
但我还是感觉yearid应该改成int型and convert(int,a.earid)=convert(int,b.yearid)-1,虽然那样也可以通过。
sum(case when 年份=2002 then 一月数量 end),
(sum(case when 年份=2002 then 二月数量 end)-sum(case when 年份=2001 then 二月数量 end))*100.0/
sum(case when 年份=2002 then 二月数量 end),
...
from 表
group by 商品编码
order by 商品编码
sl6 numeric,sl7 numeric,sl8 numeric,sl9 numeric,sl10 numeric,sl11 numeric,sl12 numeric,yearid varchar(12))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
go
delete #lfy where yearid='增长率'
go
insert into #lfy
select a.id,cast(100*(b.sl1-a.sl1-0.0)/a.sl1 as numeric(10,2)) pren1,cast(100*(b.sl2-a.sl2-0.0)/a.sl2 as numeric(10,2)) pren2,cast(100*(b.sl3-a.sl3-0.0)/a.sl3 as numeric(10,2)) pren3,
cast(100*(b.sl4-a.sl4-0.0)/a.sl4 as numeric(10,2)) pren4,cast(100*(b.sl5-a.sl5-0.0)/a.sl5 as numeric(10,2)) pren5,cast(100*(b.sl6-a.sl6-0.0)/a.sl6 as numeric(10,2)) pren6,
cast(100*(b.sl7-a.sl7-0.0)/a.sl7 as numeric(10,2)) pren7,cast(100*(b.sl8-a.sl8-0.0)/a.sl8 as numeric(10,2)) pren8,cast(100*(b.sl9-a.sl9-0.0)/a.sl9 as numeric(10,2)) pren9,
cast(100*(b.sl10-a.sl10-0.0)/a.sl10 as numeric(10,2)) pren10,cast(100*(b.sl11-a.sl11-0.0)/a.sl11 as numeric(10,2)) pren11,cast(100*(b.sl12-a.sl12-0.0)/a.sl12 as numeric(10,2)) pren12,
'增长率' from #lfy a , #lfy b where a.id=b.id and convert(int,a.yearid)=convert(int,b.yearid)-1 and a.yearid<>'增长率' and b.yearid<>'增长率'
go
select * from #lfydrop table #lfy
表之间通过商品编号和年份来关联这样考虑的话就是一个很简单的两个表之间的字段值计算的问题.--改一下大力的,实现你最后要求的结果.
create table #lfy(id int,sl1 money,sl2 money,sl3 money,sl4 money,sl5 money,
sl6 money,sl7 money,sl8 money,sl9 money,sl10 money,sl11 money,sl12 money
,yearid varchar(4))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'select id as 商品编码,sl1 as 一月数量,sl2 as 二月数量,sl3 as 三月数量
,sl4 as 四月数量,sl5 as 五月数量,sl6 as 六月数量,sl7 as 七月数量
,sl8 as 八月数量,sl9 as 九月数量,sl10 as 十月数量,sl11 as 十一月数量
,sl12 as 十二月数量,yearid as 年份
from
(
select 0 as sortid,* from #lfy
union all
select 1,a.id
,(b.sl1-a.sl1-0.0)/a.sl1
,(b.sl2-a.sl2-0.0)/a.sl2
,(b.sl3-a.sl3-0.0)/a.sl3
,(b.sl4-a.sl4-0.0)/a.sl4
,(b.sl5-a.sl5-0.0)/a.sl5
,(b.sl6-a.sl6-0.0)/a.sl6
,(b.sl7-a.sl7-0.0)/a.sl7
,(b.sl8-a.sl8-0.0)/a.sl8
,(b.sl9-a.sl9-0.0)/a.sl9
,(b.sl10-a.sl10-0.0)/a.sl10
,(b.sl11-a.sl11-0.0)/a.sl11
,(b.sl12-a.sl12-0.0)/a.sl12
,null
from #lfy a , #lfy b where a.id=b.id and a.yearid=b.yearid-1
) aa
order by id,sortid,yearid
go
drop table #lfy
,null
from #lfy a , #lfy b where a.id=b.id and a.yearid=b.yearid-1
) aa
order by id,sortid,yearid
go
drop table #lfy改为:
,'增长率']
from #lfy a , #lfy b where a.id=b.id and a.yearid=b.yearid-1
) aa
order by id,sortid,yearid
go
drop table #lfy
sl6 int,sl7 int,sl8 int,sl9 int,sl10 int,sl11 int,sl12 int,yearid varchar(4))
insert into #lfy select 1001,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1001,15,24,26,28,32,78,45,40,54,30,95,74,'2002'
insert into #lfy select 1002,10,20,23,25,26,63,35,36,45,26,84,65,'2001'
insert into #lfy select 1002,15,24,26,28,32,78,45,40,54,30,95,74,'2002'select id,sl1,sl2,sl3,sl4,sl5,sl6,sl7,sl8,sl9,sl10,sl11,sl12,yearid from (
select *,0 flag from #lfy
union all
select a.id,cast((b.sl1-a.sl1-0.0)/a.sl1 as numeric(10,2)) pren1,cast((b.sl2-a.sl2-0.0)/a.sl2 as numeric(10,2)) pren2,cast((b.sl3-a.sl3-0.0)/a.sl3 as numeric(10,2)) pren3,
cast((b.sl4-a.sl4-0.0)/a.sl4 as numeric(10,2)) pren4,cast((b.sl5-a.sl5-0.0)/a.sl5 as numeric(10,2)) pren5,cast((b.sl6-a.sl6-0.0)/a.sl6 as numeric(10,2)) pren6,
cast((b.sl7-a.sl7-0.0)/a.sl7 as numeric(10,2)) pren7,cast((b.sl8-a.sl8-0.0)/a.sl8 as numeric(10,2)) pren8,cast((b.sl9-a.sl9-0.0)/a.sl9 as numeric(10,2)) pren9,
cast((b.sl10-a.sl10-0.0)/a.sl10 as numeric(10,2)) pren10,cast((b.sl11-a.sl11-0.0)/a.sl11 as numeric(10,2)) pren11,cast((b.sl12-a.sl12-0.0)/a.sl12 as numeric(10,2)) pren12,'增长率',1
from #lfy a , #lfy b where a.id=b.id and a.yearid=b.yearid-1 ) tem order by id,flaggo
drop table #lfy