超难的一个关于计算增长值和增长率的问题！

[dreamsun]

V1 D1
3.2241 2007-07-05 00:00:00.000
3.0944 2007-07-04 00:00:00.000
3.2408 2007-07-03 00:00:00.000
3.2915 2007-07-02 00:00:00.000
3.2334 2007-06-29 00:00:00.000
3.2867 2007-06-28 00:00:00.000
3.4436 2007-06-26 00:00:00.000
3.3443 2007-06-25 00:00:00.000
3.3073 2007-06-22 00:00:00.000
D1中的日期不是连续的，有可能隔一天，或者几天。
现在想计算出当天的增长值和增长率。
增长率=(当天的值-前一次的值)/前一次的值
例如2007-07-03当天的增长率=(3.2408-3.2915)/3.2915
如果到了底部，没有上一次的值，那增长率是0 例如最底2007-06-22的下面再没有数据，那增长率为0
麻烦的地方是，上一次的值，不能用当天的日期减1做为条件来取出。
请问各位高手有什么办法？！！！

最好不要用到临时表，因为要经常查询，不断的创建删除临时表，担心会影响MSSQL的性能。

[dreamsun]

select IDENTITY(int,1,1) as num,b.* into #t from 表 b order by b.D1 desc
Select a.*,addRate=cast((a.V1-b.V1)*1.0/b.V1*100 as float)
from #ttt a
left Join #ttt b
on b.num=a.num+1
order by a.num asc

上面是我想的一个办法，用到临时表的！哪位高手有没有不用到临时表的？！告诉我啊，谢谢

[zjcxc]

DECLARE
@dt datetime

SET @dt = '2007-07-03' -- 查询的日期

-- 查询
DECLARE
@v1 decimal(12, 4),
@v2 decimal(12, 4)

-- 得到当天和当前前一次的值
SELECT TOP 2
-- 当前的值
@v1 = CASE D1 WHEN @dt THEN V1 ELSE @v1 END,
-- 前一天的值
@v2 = CASE D1 WHEN @dt THEN @v2 ELSE V1 END
FROM 表
WHERE D1 < = @dt
ORDER BY D1 DESC

-- 计算
SELECT
-- 前一次没有值就是 0
@v2 = ISNULL(@v2, 0),
-- 当前没有值取前一次的值
@v1 = ISNULL(@v1, @v2)

-- 最终结果
SELECT
CASE
-- 没有前一次的值, 结果是当天的值
WHEN @v2 = 0 THEN @v1
-- 否则的话, 是计算值
ELSE (@v1 - @v2) / @v2
END

[hellowork]

----创建测试数据
declare @t table(V1 decimal(10,4),D1 datetime)
insert @t
select 3.2241,'2007-07-05 00:00:00.000' union all
select 3.0944,'2007-07-04 00:00:00.000' union all
select 3.2408,'2007-07-03 00:00:00.000' union all
select 3.2915,'2007-07-02 00:00:00.000' union all
select 3.2334,'2007-06-29 00:00:00.000' union all
select 3.2867,'2007-06-28 00:00:00.000' union all
select 3.4436,'2007-06-26 00:00:00.000' union all
select 3.3443,'2007-06-25 00:00:00.000' union all
select 3.3073,'2007-06-22 00:00:00.000'

----查询
SELECT V1,D1,
ratio = ISNULL((V1 - (select V1 from @t where D1 = (select max(D1) from @t where D1 < a.D1)))/
(select V1 from @t where D1 = (select max(D1) from @t where D1 < a.D1)),0)
FROM @t as a

/*结果
V1 D1 ratio
------------ --------------------------------------------
3.2241 2007-07-05 00:00:00.000 .041914426059979
3.0944 2007-07-04 00:00:00.000 -.045174031103431
3.2408 2007-07-03 00:00:00.000 -.015403311560078
3.2915 2007-07-02 00:00:00.000 .017968701676254
3.2334 2007-06-29 00:00:00.000 -.016216874068214
3.2867 2007-06-28 00:00:00.000 -.045562783133929
3.4436 2007-06-26 00:00:00.000 .029692312292557
3.3443 2007-06-25 00:00:00.000 .011187373386145
3.3073 2007-06-22 00:00:00.000 .000000000000000
*/

[hellowork]

----创建测试数据
declare @t table(V1 decimal(10,4),D1 datetime)
insert @t
select 3.2241,'2007-07-05 00:00:00.000' union all
select 3.0944,'2007-07-04 00:00:00.000' union all
select 3.2408,'2007-07-03 00:00:00.000' union all
select 3.2915,'2007-07-02 00:00:00.000' union all
select 3.2334,'2007-06-29 00:00:00.000' union all
select 3.2867,'2007-06-28 00:00:00.000' union all
select 3.4436,'2007-06-26 00:00:00.000' union all
select 3.3443,'2007-06-25 00:00:00.000' union all
select 3.3073,'2007-06-22 00:00:00.000'

----方法2:
SELECT a.V1,a.D1,
ratio = ISNULL((a.V1 - b.V1)/b.V1,0)
FROM @t as a LEFT JOIN @t as b
ON b.D1 = (select max(D1) from @t where D1 < a.D1)


/*结果
V1 D1 ratio
------------ --------------------------------------------
3.2241 2007-07-05 00:00:00.000 .041914426059979
3.0944 2007-07-04 00:00:00.000 -.045174031103431
3.2408 2007-07-03 00:00:00.000 -.015403311560078
3.2915 2007-07-02 00:00:00.000 .017968701676254
3.2334 2007-06-29 00:00:00.000 -.016216874068214
3.2867 2007-06-28 00:00:00.000 -.045562783133929
3.4436 2007-06-26 00:00:00.000 .029692312292557
3.3443 2007-06-25 00:00:00.000 .011187373386145
3.3073 2007-06-22 00:00:00.000 .000000000000000
*/

[playwarcraft]

declare @t table(V1 decimal(10,4),D1 datetime)
insert @t
select 3.2241,'2007-07-05 00:00:00.000' union all
select 3.0944,'2007-07-04 00:00:00.000' union all
select 3.2408,'2007-07-03 00:00:00.000' union all
select 3.2915,'2007-07-02 00:00:00.000' union all
select 3.2334,'2007-06-29 00:00:00.000' union all
select 3.2867,'2007-06-28 00:00:00.000' union all
select 3.4436,'2007-06-26 00:00:00.000' union all
select 3.3443,'2007-06-25 00:00:00.000' union all
select 3.3073,'2007-06-22 00:00:00.000'


select V1,D1,
case when isnull((select top 1 V1 from @t where D1<a.D1 order by D1 desc),0)=0
then 0
else (V1-isnull((select top 1 V1 from @t where D1<a.D1 order by D1 desc),0))/isnull((select top 1 V1 from @t where D1<a.D1 order by D1 desc),0) end as range
from @t a

/*
3.2241 2007-07-05 00:00:00.000 .041914426059979
3.0944 2007-07-04 00:00:00.000 -.045174031103431
3.2408 2007-07-03 00:00:00.000 -.015403311560078
3.2915 2007-07-02 00:00:00.000 .017968701676254
3.2334 2007-06-29 00:00:00.000 -.016216874068214
3.2867 2007-06-28 00:00:00.000 -.045562783133929
3.4436 2007-06-26 00:00:00.000 .029692312292557
3.3443 2007-06-25 00:00:00.000 .011187373386145
3.3073 2007-06-22 00:00:00.000 .000000000000000

*/

[萧霖]

学习

[tom_cat007]

有可能的话改下表结构，把前次的金额记下来，查询的时候直接算下。

[chbvb4302]

用一条语句可以帮你搞定,记的给分哦,经过调试的
create table test
(v1 decimal (18,4),d1 dateTime )
insert into test values (3.234,'2007-09-09')
insert into test values (3.134,'2007-09-08')
insert into test values(3.034,'2007-09-07')
insert into test values(3.534,'2007-09-06')

select a.d1,isnull((select case when a.v1 is null then 0 else (a.v1-b.v1)/b.v1 end from test b
where datediff(d, b.d1 ,a.d1)=1),0) as 增长率
from test a

[lt1129]

declare @t1 datetime,@t2 datetime,@v1 decimal(12, 4),@v2 decimal(12, 4)

set @t1='2007-06-28'
set @t2 =(select top 1 D1 from t where D1<@t1 order by D1 desc)

set @v1 =(select V1 from t where D1=convert(varchar(10),@t1,120))
set @v2 =(select v1 from t where D1=@t2)

select @t1 as 当天,
@v1 as 当天值,
@t2 前一天,
@v2 前一天值,
case when @v2 is null then @v1 else (@v1-@v2) end as 增长值,
case when @v2 is null then 0 else ((@v1-@v2)/@v2) end as 增长率
--结果：
当天 当天值 前一天 前一天值 增长值 增长率
----------- ---------- ------------ -------- ------- -------------------
2007-06-28 3.2867 2007-06-26 3.4436 -.1569 -.04556278313392960