商品统计中很NB的SQL需要

Question

Create table test
(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
---------------------------我的问题---------------
同比是:本月的和去年的本月比
环比是:(本月的-上月)/上月 要求2个小数位
累计是:当年每个月的数据加起来
要求得到 条件只有年和月,假若条件是:2010年5月,则结果
名字    数量(A),数量(B),当月同比(A),当月同比(B),当月环比(A),当月环比(B),累计数量(A),累计数量(B),累计当月同比(A),累计当月同比(B),累计当月环比(A),累计当月环比(B)
phone1   500     500     1               1       0.25         0.25       6500                      12                              0+1/1+1/2+1/3+1/4+1/5+1/6+1/7+1/8+1/9+1/10+1/11
求MSSQL2005语句怎么写(注,从个人来说,需要是比较BUG的,不知大师们可有NB方法)

Answer 1

Create table test
(ids bigint identity(1,1) primary key,
names nvarchar(20),--名字
classify nvarchar(10),--分类
years int,--年
months int,--月
num bigint--数量
)
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  godeclare @year as int
declare @month as int
set @year = 2010
set @month = 5select 名字 = names,
       分类 = classify ,
       数量 = m.num ,
       当月同比 = cast(m.num * 1.0 / (select 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)), 
       当月环比 = cast((m.num - (select top 1 num from test n where n.names = m.names and n.classify = m.classify and ltrim(n.years) + right('0'+ltrim(n.months),2) < ltrim(m.years) + right('0'+ltrim(m.months),2) order by n.years desc , n.months desc))*1.0/
                  (select top 1 num from test n where n.names = m.names and n.classify = m.classify and ltrim(n.years) + right('0'+ltrim(n.months),2) < ltrim(m.years) + right('0'+ltrim(m.months),2) order by n.years desc , n.months desc) as decimal(18,2)),
       累计数量 = (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months),
       累计当月同比 = cast((select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months) * 1.0 / 
                      (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months) as decimal(18,2)),
       累计当月环比 = cast(((select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years and n.months <= m.months) - (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months))*1.0/
                      (select sum(num) from test n where n.names = m.names and n.classify = m.classify and n.years = m.years - 1 and n.months <= m.months) as decimal(18,2))
from test m 
where m.years = @year and m.months = @monthdrop table test/*
名字                   分类         数量                   当月同比                 当月环比                 累计数量                 累计当月同比               累计当月环比               
-------------------- ---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
phone1               A          500                  1.00                 .25                  1500                 1.00                 .00
phone1               B          500                  1.00                 .25                  1500                 1.00                 .00（所影响的行数为 2 行）
*/