The SQL statement cyclically processes the data of the current row and the data of the previous row plus or minus, and the SQL statement

Source: Internet
Author: User

The SQL statement cyclically processes the data of the current row and the data of the previous row plus or minus, and the SQL statement

In the following example, we use a cursor to cycle the data in Table # temptable, and then add argument1 in the current row and the previous row to the argument2 in the current row, which is relatively simple.

--drop table #temptablecreate table #temptable(  argument1 int,  argument2 int,  argument3 datetime)declare @rowcount int,@argument1 int,@argument2 nvarchar(50),@argument3 datetimeset @rowcount=1set @argument1=1set @argument2=0set @argument3=GETDATE()while(@rowcount<100)begin  insert into #temptable(argument1,argument2,argument3)        values(@argument1,@argument2,@argument3)    set @argument1=@argument1 + datepart(day,@argument3)  set @argument3=@argument3-1    set @rowcount = @rowcount + 1end--select * from #temptabledeclare @lastargument2 intset @lastargument2=0set @argument2=0declare _cursor cursor for(select argument1 from #temptable)open _cursor;fetch next from _cursor into @argument2 while @@fetch_status = 0begin        update #temptable  set argument2=@argument2+@lastargument2  where current of _cursor    set @lastargument2=@argument2    fetch next from _cursor into @argument2 endclose _cursordeallocate _cursor--select * from #temptable

Ask a question:

The first fetch next from _ cursor into @ argument2 sentence cannot be placed in the first row of the while loop. Why is the second row deleted? I remember that I had an error here.


Add or subtract a quantitative implementation statement for SQL database fields

Update table_name set PAS00000000055956 = PAS00000000055956 + '000000' where...

If your field type is correct

Then you can read the data and then operate
If PHP is used for development, you can easily select data segment operations based on data features.
Then write data. I am also a beginner, and I usually process it like this.

However, I have another idea about your problem, that is, to store only the numbers in the database, calculate the numbers, and then judge and process the input when reading the data.

This is the correct idea of programming: Physical Layer + logical layer + output layer.
A large program can be defined by the three layers, and the idea can be clear.

In SQL, how can I get the value of this row minus the value of the previous row? Multiple rows can be processed cyclically. Thank you!

Create table biao22 (a int)

Insert biao22 select '10'

Insert biao22 select '9'

Insert biao22 select '20'

Insert biao22 select '25'

Insert biao22 select '18'

Insert biao22 select '7'

Select * from biao22

Create table # A (id int identity (1, 1), a int)

Insert into # a ()
Select * from biao22

Declare @ a int
Declare @ B int

Select @ a = min (id) from #

Select @ B = max (id) from #

While (@ a <@ B)

Begin

Select t1.a-t2.a from # a t1, # a t2 where t1.id = t2.id + 1 and t1.id = @

Set @ a = @ a + 1

End

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.