还是那个帖子
http://topic.csdn.net/u/20110118/17/67a0ff10-124d-4c9f-9a8c-17de7e001c01.html
里面没有处理多重复数据的问题,如:insert into test select ...再执行一次,我用的是select 名字 = names,
分类 = classify ,
数量 = sum(m.num)....
前面搞定了,后面又错了,还你处理吧,等待
http://topic.csdn.net/u/20110118/17/67a0ff10-124d-4c9f-9a8c-17de7e001c01.html
里面没有处理多重复数据的问题,如:insert into test select ...再执行一次,我用的是select 名字 = names,
分类 = classify ,
数量 = sum(m.num)....
前面搞定了,后面又错了,还你处理吧,等待
分类 = classify ,
数量=(select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = @year and n.months = @month),
当月同比=cast( (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = @year and n.months = @month)*1.0/
(select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = @year - 1 and n.months = @month) as decimal(18,2))
from test m
where m.years = @year and m.months = @month group by names,classify
第一步完成这样了,我接着再试下其它
(ids bigint identity(1,1) primary key,
names nvarchar(20),--名字
classify nvarchar(10),--分类
years int,--年
months int,--月
num bigint--数量
)
go
--测试数据(我看高手都喜欢这样写)-------
insert into test select 'phone1','A','2009',1,100 union all
select 'phone1','A','2009',2,200 union all
select 'phone1','A','2009',3,300 union all
select 'phone1','A','2009',4,400 union all
select 'phone1','A','2009',5,500 union all
select 'phone1','A','2009',6,600 union all
select 'phone1','A','2009',7,700 union all
select 'phone1','A','2009',8,800 union all
select 'phone1','A','2009',9,900 union all
select 'phone1','A','2009',10,1000 union all
select 'phone1','A','2009',11,1100 union all
select 'phone1','A','2009',12,1200 union all
select 'phone1','A','2010',1,100 union all
select 'phone1','A','2010',2,200 union all
select 'phone1','A','2010',3,300 union all
select 'phone1','A','2010',4,400 union all
select 'phone1','A','2010',5,500 union all
select 'phone1','A','2010',6,600 union all
select 'phone1','A','2010',7,700 union all
select 'phone1','A','2010',8,800 union all
select 'phone1','A','2010',9,900 union all
select 'phone1','A','2010',10,1000 union all
select 'phone1','A','2010',11,1100 union all
select 'phone1','A','2010',12,1200 union all
select 'phone1','B','2009',1,100 union all
select 'phone1','B','2009',2,200 union all
select 'phone1','B','2009',3,300 union all
select 'phone1','B','2009',4,400 union all
select 'phone1','B','2009',5,500 union all
select 'phone1','B','2009',6,600 union all
select 'phone1','B','2009',7,700 union all
select 'phone1','B','2009',8,800 union all
select 'phone1','B','2009',9,900 union all
select 'phone1','B','2009',10,1000 union all
select 'phone1','B','2009',11,1100 union all
select 'phone1','B','2009',12,1200 union all
select 'phone1','B','2010',1,100 union all
select 'phone1','B','2010',2,200 union all
select 'phone1','B','2010',3,300 union all
select 'phone1','B','2010',4,400 union all
select 'phone1','B','2010',5,500 union all
select 'phone1','B','2010',6,600 union all
select 'phone1','B','2010',7,700 union all
select 'phone1','B','2010',8,800 union all
select 'phone1','B','2010',9,900 union all
select 'phone1','B','2010',10,1000 union all
select 'phone1','B','2010',11,1100 union all
select 'phone1','B','2010',12,1200
go
declare @y int,@m int
set @y=2010
set @m=5
;with c1 as(
select years,months,names,classify,sum(num)as s from test where years between @y-1 and @y group by years,months,names,classify
),c2 as(
select (years-@y+1)*12+months as ym,names,classify,s from c1
),c3 as(
select a.ym,a.names,a.classify,a.s,convert(decimal(8,2),1.0*(a.s-b.s)/b.s) hb,convert(decimal(8,2),1.0*a.s/c.s) tb
from c2 a inner join c2 b on a.names=b.names and a.classify=b.classify and a.ym=b.ym+1
inner join c2 c on a.names=c.names and a.classify=c.classify and a.ym=c.ym+12 where a.ym<=12+@m
),c4 as(
select names,classify,sum(s)ss,sum(hb)sh,sum(tb)st from c3 group by names,classify
)
select distinct t1.names,t1.classify,t1.s 数量,t1.hb 环比,t1.tb 同比,t2.ss 当年累计,t2.sh 当年环比累计,t2.st 当年同比累计 from c3 t1 inner join c4 t2 on t2.names=t1.names and t2.classify=t2.classify
where t1.ym=12+@m
go
drop table test
/*
names classify 数量 环比 同比 当年累计 当年环比累计 当年同比累计
-------------------- ---------- -------------------- --------------------------------------- --------------------------------------- -------------------- --------------------------------------- ---------------------------------------
phone1 A 500 0.25 1.00 1500 1.16 5.00
phone1 B 500 0.25 1.00 1500 1.16 5.00(2 行受影响)*/