获得先前记录值和当前记录值的差异 [英] Get Difference of Previous Record Value and Current Record Value

问题描述

亲爱的朋友们，



我有数据



月增长
$ b $ 5 SEP 5％

OCT 6％

NOV 2％

DEC 9％


我想将上个月与下个月进行比较，并且需要显示它是否增长。

例如。 OCT是增长（OCT-SEP），NOV是去增长（NOV-OCT）

如何获得它，请指导

Dear Friends,

I am having data of

Month Growth
SEP 5%
OCT 6%
NOV 2%
DEC 9%

I want to compare the previous month with next month and need to show it is grown or not.
Eg. OCT is Growth (OCT-SEP) and NOV is De-Growth (NOV-OCT)
How to get it, please guide

推荐答案

试试这个

Try this

create    table test 
(
 month varchar(25) , growth int 
)

insert into test ( month, growth ) values ('sep' , 5)
insert into test ( month, growth ) values ('oxt' , 6)
insert into test ( month, growth ) values ('nov' , 2)
insert into test ( month, growth ) values ('dec' , 9)


Declare @prevmonthgrowth as int
Declare @month as  varchar(33)
Declare @currentgrowth as int
 
 declare @tbl table ( month varchar( 25) , growth int , result varchar(44) ) 
Declare MY_data CURSOR FOR
 
Select month ,growth from  test (Nolock)
 set @prevmonthgrowth = 0
OPEN MY_data
    FETCH NEXT FROM MY_data INTO @month ,@currentgrowth
        WHILE @@FETCH_STATUS = 0
        BEGIN
          
		  if(  @currentgrowth > @prevmonthgrowth )
		  insert into @tbl ( month , growth ,result ) values ( @month , @currentgrowth , 'growth');
		  else
		  insert into @tbl ( month , growth ,result ) values ( @month , @currentgrowth , 'no growth');
          set @prevmonthgrowth =   @currentgrowth          
        FETCH NEXT FROM MY_data INTO @month ,@currentgrowth
        END
    CLOSE MY_data
DEALLOCATE MY_data
select * from @tbl

另一个答案在效率方面非常糟糕。试试这个：

The other answer is really terrible in terms of efficiency. Try this:

create    table test
(
id int identity,
 month varchar(25) , growth int
)

insert into test ( month, growth ) values ('sep' , 5)
insert into test ( month, growth ) values ('oct' , 6)
insert into test ( month, growth ) values ('nov' , 2)
insert into test ( month, growth ) values ('dec' , 9)

with growth
as
(
select month, growth, lag(growth, 1, null) over (order by id) as prev from test
)

select month, growth, case when prev is null then 'first value' when growth - prev > 1 then 'increased' when growth = prev then 'same' else 'decreased' end as result
from growth

这适用于SQL Server请注意，我添加了一个id列，如果没有，您需要找到另一种方法来定义排序顺序，以便按月排序。

This works for SQL Server 2012. Note that I added an id column, without that, you need to find another way to define the sort order so that you sort by month.

这篇关于获得先前记录值和当前记录值的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！