如何根据一个表生成同期对比数据....
表t1字段如下
title   content   salesprice   addtime
-----------------------------------
想生成如下格式的，根据月份，对比去年和今年的salesprice，比例和增长率（下面内容我乱打的..）最后还有一行合计。

月份   去年(2005)   今年(2006)   比例   增长率  
一月   35000.00       353454           39.68%
二月   0                     0                     39.68%
三月   0                     0                     39.68%
四月   63000.00       25000.00     39.68%   -60.31%  
五月   148000.00     0%   -100%  
六月   60000.00       0%   -100%  
七月   110000.00     0%   -100%  
八月   250000.00     35000.00     14%   -86%  
九月   56000.00       0%   -100%  
十月   85000.00       0%   -100%  
十一月   100000.00       0%   -100%  
十二月   168000.00       0%   -100%  
合计   1040000   95000   9.13%   -90.86%  


------解决方案--------------------
最后两列不知道你要怎么计算,用下面得到得去年和今年得值做一下就行了.
-----------
select a.mon as 月份,a.salesprice as 去年,b.salesprice as 今年
from
(select right(convert(char(6),addtime,112),2) as mon,sum(salesprice) as salesprice
from t1
where addtime between '2005 ' and '2006 '
group by mon) a
join
(select right(convert(char(6),addtime,112),2) as mon,sum(salesprice) as salesprice
from t1
where addtime between '2006 ' and '2007 '
group by mon) b on a.mon = b.mon
------解决方案--------------------
我的思路大致是这样，分三步

1，先把月份，去年，今年三列列出来
select * into #t1 from
(select distinct month(addtime) 月份,
case addtime when year(addtime)=year(getdate())-1 then salesprice end 去年,
case addtime when year(addtime)=year(getdate()) then salesprice end 今年
from
tablename
where year(addtime)=year(getdate())-1 or addtime when year(addtime)=year(getdate())
group by month(addtime)
order by month(addtime))a1

--
2，按照以上的结果集计算生成比例和增长率两列
select * into #t2 from
(select *,
cast(今年 as decimal(10,2))/cast(去年 as decimal(10,2)) 比例,
cast(((cast(今年 as decimal(10,2))-cast(去年 as decimal(10,2)))/cast(去年 as decimal(10,2))/100) as varchar(50)) as 增长率
from #t1)a2
--
3，把汇总的一行得出并与以上union all
select * from #t2
union all
select '合计 ',sum(去年),sum(今年),sum(比例),sum(增长率) from #t2


只是个大致思路，其中难免错误，能力有限，当前只能给与这一点支持了：（
------解决方案--------------------

SELECT 月份,去年,今年,比例,增长率
FROM
(SELECT '合计 ' AS 月份,
SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE())-1 THEN salesprice END) AS 去年,
SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE()) THEN salesprice END) AS 今年,
... AS 比例,--不知道比例是什么概念?
CASE SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE())-1 THEN salesprice END)
WHEN 0 THEN 0
ELSE
( SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE()) THEN salesprice END)
- SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE())-1 THEN salesprice END) )
/ SUM(CASE YEAR(addtime) WHEN YEAR(GETDATE())-1 THEN salesprice END) END AS 增长率,
1 AS sort
FROM t1
UNION ALL
SELECT MONTH(addtime),
SUM(CASE