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的性能。
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的性能。
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上面是我想的一个办法,用到临时表的!哪位高手有没有不用到临时表的?!告诉我啊,谢谢
DECLARE
@dt datetimeSET @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
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
*/
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
*/
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*/
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
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