求一个关于数据增长率的SQL

[legendlovehao]

RT 打个比方 表结构如下 求表每一年同比数据增长率 就是当年数据总数 减去 去年数据总数 在除以去年数据总数
当他去年没有数据为0的时候就显示1
表： testa
ID year(年份)
1 2012-01
2 2013-02
3 2013-03
4 2014-04
我写的SQL
select a1.SJ,a1.zl as zl1,a2.zl as zl2,case when a2.zl = 0 then 1 else ROUND( (a1.zl-a2.zl)/a2.zl,4) end as zzl from
(select YEAR(a.adddate) as SJ,COUNT(0) as zl from testa a group by YEAR(adddate)) a1,
(select * from (select top 1 YEAR(adddate) as SJ,0 as zl from testa a order by YEAR(adddate)
) a11
union all
select (YEAR(adddate)+1) as SJ,COUNT(0) as zl from testa a group by YEAR(adddate)) a2
where a1.SJ=a2.SJ and a2.SJ is not null 有问题！ 执行数据库为SQL2000
求条SQL！！！

[xiaodongni]

years qty ----------- ----------- 2002 100 2003 110 2004 120 2005 130 2006 145 2007 165 2008 167 自己建立的数据库 如果原始数据时间列有很多时间。可以对year(colnum)分组求出每年的数据。 with testno as (select years,qty,ROW_NUMBER() over(order by years)as n from testa) select t1.years, case t1.n when '1' then '1' else 1.00*(t1.qty-t2.qty)/t2.qty end as growth from testno as t1 left join testno as t2 on t1.n=t2.n+1。

[专注or全面]

create table #t
(
	id int,
	vdate varchar(10),
	qty int
)

insert into #t values (1,'2012-1',10);
insert into #t values (2,'2012-3',20);
insert into #t values (3,'2013-5',30);
insert into #t values (4,'2013-7',40);
insert into #t values (5,'2013-9',50)
insert into #t values (6,'2014-1',10);
insert into #t values (7,'2014-3',20);
insert into #t values (8,'2014-5',30);
insert into #t values (9,'2014-7',40);
insert into #t values (10,'2014-9',50)

select * from #t

--当年数据总数 减去 去年数据总数 在除以去年数据总数

--当他去年没有数据为0的时候就显示1


select t1.v_year as 当前年份,t1.v_sum as 当前年份数据,t2.v_year as 去年年份,t2.v_sum 去年数据,t1.v_sum-t2.v_sum 差值 from
(
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t1 left join 
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t2 on t1.v_year=t2.v_year+1
)

当前年份 当前年份数据      去年年份 去年数据        差值
---- ----------- ---- ----------- -----------
2012          30          NULL        NULL        NULL
2013          120         2012        30          90
2014          150         2013        120         30

(3 行受影响)


--其他加减乘除的啊你自己算好了

[专注or全面]

select t1.v_year as 当前年份,t1.v_sum as 当前年份数据,t2.v_year as 去年年份,t2.v_sum 去年数据,
t1.v_sum-t2.v_sum 差值,
cast( isnull(( cast(t1.v_sum as decimal(5,2))-cast(t2.v_sum as decimal(5,2)))/t2.v_sum,1) as decimal(10,3)) as  增长率 from
(
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t1 left join 
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t2 on t1.v_year=t2.v_year+1
)

[legendlovehao]

引用 2 楼 x_wy46 的回复:

create table #t
(
	id int,
	vdate varchar(10),
	qty int
)

insert into #t values (1,'2012-1',10);
insert into #t values (2,'2012-3',20);
insert into #t values (3,'2013-5',30);
insert into #t values (4,'2013-7',40);
insert into #t values (5,'2013-9',50)
insert into #t values (6,'2014-1',10);
insert into #t values (7,'2014-3',20);
insert into #t values (8,'2014-5',30);
insert into #t values (9,'2014-7',40);
insert into #t values (10,'2014-9',50)

select * from #t

--当年数据总数 减去 去年数据总数 在除以去年数据总数

--当他去年没有数据为0的时候就显示1


select t1.v_year as 当前年份,t1.v_sum as 当前年份数据,t2.v_year as 去年年份,t2.v_sum 去年数据,t1.v_sum-t2.v_sum 差值 from
(
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t1 left join 
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t2 on t1.v_year=t2.v_year+1
)

当前年份 当前年份数据      去年年份 去年数据        差值
---- ----------- ---- ----------- -----------
2012          30          NULL        NULL        NULL
2013          120         2012        30          90
2014          150         2013        120         30

(3 行受影响)


--其他加减乘除的啊你自己算好了

为什么我计算 计算不出负数呢 ！ ROUND((a1.zl-a2.zl)/a2.zl,4)， Convert(decimal(18,2),(a1.zl-a2.zl)/a2.zl) 这是我使用的俩种计算方式 可是都计算不出来负数 只要a1.zl-a2.zl这个小于0就得出0了

[专注or全面]

引用 5 楼 legendlovehao 的回复:

[quote=引用 2 楼 x_wy46 的回复:]

create table #t
(
	id int,
	vdate varchar(10),
	qty int
)

insert into #t values (1,'2012-1',10);
insert into #t values (2,'2012-3',20);
insert into #t values (3,'2013-5',30);
insert into #t values (4,'2013-7',40);
insert into #t values (5,'2013-9',50)
insert into #t values (6,'2014-1',10);
insert into #t values (7,'2014-3',20);
insert into #t values (8,'2014-5',30);
insert into #t values (9,'2014-7',40);
insert into #t values (10,'2014-9',50)

select * from #t

--当年数据总数 减去 去年数据总数 在除以去年数据总数

--当他去年没有数据为0的时候就显示1


select t1.v_year as 当前年份,t1.v_sum as 当前年份数据,t2.v_year as 去年年份,t2.v_sum 去年数据,t1.v_sum-t2.v_sum 差值 from
(
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t1 left join 
	(
		select distinct cast(vdate as char(4)) as v_year,sum(qty)over (partition by cast(vdate as char(4))) v_sum from #t
	)t2 on t1.v_year=t2.v_year+1
)

当前年份 当前年份数据      去年年份 去年数据        差值
---- ----------- ---- ----------- -----------
2012          30          NULL        NULL        NULL
2013          120         2012        30          90
2014          150         2013        120         30

(3 行受影响)


--其他加减乘除的啊你自己算好了

为什么我计算 计算不出负数呢 ！ ROUND((a1.zl-a2.zl)/a2.zl,4)， Convert(decimal(18,2),(a1.zl-a2.zl)/a2.zl) 这是我使用的俩种计算方式 可是都计算不出来负数 只要a1.zl-a2.zl这个小于0就得出0了[/quote] 你自己调试了，每次别运算太多的步骤，第一步加减，第二步乘除…… 确认结果无误后在加一步运算……