如何更新上一行的值 [英] How to update value from previous row

问题描述

我有一张这样的桌子

I have a table like this

SNo	NetAmt
1	0
2	0
3	-10761
4	0
5	38834
6	0
7	0
8	-100000
9	0
10	80000
11	28457
12	0
13	0
14	0
15	0

我想要一个这样的结果

I want a result like this

SNo	NetAmt
1	0
2	0
3	-10761
4	-10761
5	38834
6	38834
7	38834
8	-100000
9	-100000
10	80000
11	28457
12	28457
13	28457
14	28457
15	28457

（即）如果NetAmt值为0我必须将前一行值更新为下一个非零值。

(ie) If the NetAmt value is 0 I have to update the previous row value upto next non zero value.

推荐答案

你好，

试试这种方式

Hello ,
Try this way

create table #tmp //Creating one Temporary table
(
sno int,
netamt decimal(10,2)
)

//Inserting records
insert into #tmp values
(1,0),
(2,0),
(3,	-10761),
(4,	0),
(5,	38834),
(6,	0),
(7,	0),
(8,	-100000),
(9,	0),
(10,	80000),
(11,	28457),
(12,	0),
(13,	0),
(14,	0),
(15,	0)

现在制作逻辑通过简单循环

Now make the logic by simple loop

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(sno) FROM #tmp

WHILE (@I <= @totalRecords) //let take one while loop to check the record count
BEGIN

	declare @curval decimal(8,2)
	declare @nextval decimal(8,2)
	declare @preval decimal(8,2)
	select @curval=netamt from #tmp where sno=@I

	IF (@I<@totalRecords) //for all record except last record , check whether the next value is 0 or not , if 0 then replace by previous value
		BEGIN
			declare @j int			
			set @j=@I+1
			select @nextval=netamt from #tmp where sno=@J
			if(@nextval=0)
				begin
					update #tmp set netamt=@curval where sno=@j
				end
			
		END
	ELSE if (@I=@totalRecords) //for last record  , check whether the current value is 0 or not , if 0 then replace by previous value
		BEGIN
			declare @k int			
			set @k=@I-1
			select @preval=netamt from #tmp where sno=@k
			if(@curval=0)
				begin
					update #tmp set netamt=@preval where sno=@i
				end			
		END
		SELECT @I = @I + 1	 // variable increment 
END

O / P将是

The O/P will be

sno	netamt
1	0.00
2	0.00
3	-10761.00
4	-10761.00
5	38834.00
6	38834.00
7	38834.00
8	-100000.00
9	-100000.00
10	80000.00
11	28457.00
12	28457.00
13	28457.00
14	28457.00
15	28457.00

谢谢

Thanks

如果ID已相应，将在查询中获取上述内容。

在这种情况下，我会使用存储过程来处理它和一个光标。



所以伪代码将是





previousval = 0;



开始光标



如果（previousval<> NetAmt）

if（NetAmt = 0）

update set Netamt = previousval其中id = @ id

结束如果

previousval = NetAmt

结束如果



结束光标

The above would be obtained in a query if the ID would have been accordingly.
In this case, I would approach it with a Stored procedure and a cursor.

so pseudo code will be


previousval=0;

Start cursor

If(previousval<>NetAmt)
if(NetAmt=0)
update set Netamt =previousval where id=@id
End If
previousval=NetAmt
End if

End Cursor

在此发布相同的注释解决方案，并附带适当的SQL重点。

Posting the same Solution of comments here with proper highlights of SQL.

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1
SELECT @totalRecords = COUNT(sno) FROM tmp1
declare @preval decimal(8,2)
set @preval=0
WHILE (@I <= @totalRecords) 
BEGIN

declare @curval decimal(8,2)
declare @nextval decimal(8,2)

select @curval=netamt from tmp1 where sno=@I


if(@preval<>@curval)
Begin
If (@curval=0)
Begin
update tmp1 set netamt=@preval where sno=@I
End
End

if(@curval<>0)
Begin
Select @preval=@curval
End

set @I=@I+1;
END

这篇关于如何更新上一行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！