---> 测试数据:#tb IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([年份] INT,[金额] INT) INSERT #tb SELECT 2012,100 UNION ALL SELECT 2011,50 --------------开始查询--------------------------SELECT CAST([年份] AS VARCHAR(10)) AS [年份],CAST([金额] AS VARCHAR(10)) AS [金额] FROM #tb UNION ALL SELECT '增长额',CAST(((SELECT SUM([金额]) FROM #tb WHERE [年份]=2012)-(SELECT SUM([金额]) FROM #tb WHERE [年份]=2011)) AS VARCHAR(10)) UNION ALL SELECT '增长率',LTRIM(CAST(( (SELECT SUM([金额]) FROM #tb WHERE [年份]=2012) - (SELECT SUM([金额]) FROM #tb WHERE [年份]=2011) )*100.0/ (SELECT SUM([金额]) FROM #tb WHERE [年份]=2012) AS DECIMAL(9,2) ) )+'%' ----------------结果---------------------------- /* 年份 金额 2012 100 2011 50 增长额 50 增长率 50.00% */
if OBJECT_ID('test') is not null drop table test go create table test( 年份 int, 金额 int ) go insert test select 2009,50 union all select 2010,60 union all select 2011,70 union all select 2012,100 union all select 2013,90 union all select 2014,120 go with t as( select px=ROW_NUMBER()over(order by 年份 asc), 年份, 金额 from test ), m as( select px, LTRIM(年份) as 年份, LTRIM(金额) as 金额 from t union all select px, ltrim(年份)+'增长额', ltrim(isnull(金额-(select 金额 from t b where b.px=a.px-1),0)) from t a union all select px, ltrim(年份)+'增长率', left(ltrim(isnull(isnull(金额-(select 金额 from t b where b.px=a.px-1),0)*100.0/(select 金额 from t c where c.px=a.px-1),0)),6)+'%' from t a ) select 年份, 金额 from m order by px /* 年份 金额 ------------------ ------------- 2009 50 2009增长额 0 2009增长率 0.0000% 2010增长率 20.000% 2010增长额 10 2010 60 2011 70 2011增长额 10 2011增长率 16.666% 2012增长率 42.857% 2012增长额 30 2012 100 2013 90 2013增长额 -10 2013增长率 -10.00% 2014增长率 33.333% 2014增长额 30 2014 120(18 行受影响) */
楼上的方法不错! 如果需要以:年份 增长额 增长率这种形式显示可以看看下面的代码--数据借用上面姐姐的 if OBJECT_ID('test') is not null drop table test go create table test( 年份 int, 金额 int ) go insert test select 2009,50 union all select 2010,60 union all select 2011,70 union all select 2012,100 union all select 2013,90 union all select 2014,120 go;with T as( select a.年份 as 年份1,a.金额 as 金额1,b.年份 as 年份2,b.金额 as 金额2 from test as a,test as b where a.年份+1=b.年份 ) select 年份1 as 年份,isnull(金额2,0)-isnull(金额1,0) as 增长额,(convert(nvarchar(10),cast((isnull(金额2,0)-isnull(金额1,0))*100.0/金额1 as decimal(18,2)))+'%') as 增长率 from T /* 年份 增长额 增长率 2009 10 20.00% 2010 10 16.67% 2011 30 42.86% 2012 -10 -10.00% 2013 30 33.33% */
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([年份] INT,[金额] INT)
INSERT #tb
SELECT 2012,100 UNION ALL
SELECT 2011,50
--------------开始查询--------------------------SELECT CAST([年份] AS VARCHAR(10)) AS [年份],CAST([金额] AS VARCHAR(10)) AS [金额] FROM #tb
UNION ALL
SELECT '增长额',CAST(((SELECT SUM([金额]) FROM #tb WHERE [年份]=2012)-(SELECT SUM([金额]) FROM #tb WHERE [年份]=2011)) AS VARCHAR(10))
UNION ALL
SELECT '增长率',LTRIM(CAST((
(SELECT SUM([金额]) FROM #tb WHERE [年份]=2012)
-
(SELECT SUM([金额]) FROM #tb WHERE [年份]=2011)
)*100.0/
(SELECT SUM([金额]) FROM #tb WHERE [年份]=2012)
AS DECIMAL(9,2)
)
)+'%'
----------------结果----------------------------
/*
年份 金额
2012 100
2011 50
增长额 50
增长率 50.00%
*/
if OBJECT_ID('test') is not null
drop table test
go
create table test(
年份 int,
金额 int
)
go
insert test
select 2009,50 union all
select 2010,60 union all
select 2011,70 union all
select 2012,100 union all
select 2013,90 union all
select 2014,120
go
with t
as(
select
px=ROW_NUMBER()over(order by 年份 asc),
年份,
金额
from
test
),
m
as(
select
px,
LTRIM(年份) as 年份,
LTRIM(金额) as 金额
from
t
union all
select
px,
ltrim(年份)+'增长额',
ltrim(isnull(金额-(select 金额 from t b where b.px=a.px-1),0))
from
t a
union all
select
px,
ltrim(年份)+'增长率',
left(ltrim(isnull(isnull(金额-(select 金额 from t b where b.px=a.px-1),0)*100.0/(select 金额 from t c where c.px=a.px-1),0)),6)+'%'
from
t a
)
select
年份,
金额
from
m
order by px
/*
年份 金额
------------------ -------------
2009 50
2009增长额 0
2009增长率 0.0000%
2010增长率 20.000%
2010增长额 10
2010 60
2011 70
2011增长额 10
2011增长率 16.666%
2012增长率 42.857%
2012增长额 30
2012 100
2013 90
2013增长额 -10
2013增长率 -10.00%
2014增长率 33.333%
2014增长额 30
2014 120(18 行受影响)
*/
if OBJECT_ID('test') is not null
drop table test
go
create table test(
年份 int,
金额 int
)
go
insert test
select 2009,50 union all
select 2010,60 union all
select 2011,70 union all
select 2012,100 union all
select 2013,90 union all
select 2014,120
go;with T as(
select a.年份 as 年份1,a.金额 as 金额1,b.年份 as 年份2,b.金额 as 金额2 from test as a,test as b
where a.年份+1=b.年份
)
select 年份1 as 年份,isnull(金额2,0)-isnull(金额1,0) as 增长额,(convert(nvarchar(10),cast((isnull(金额2,0)-isnull(金额1,0))*100.0/金额1 as decimal(18,2)))+'%') as 增长率 from T
/*
年份 增长额 增长率
2009 10 20.00%
2010 10 16.67%
2011 30 42.86%
2012 -10 -10.00%
2013 30 33.33%
*/