Ask how to write SQL and calculate the balance for each row of data

Implementation idea: If the last one is taken out in the normal order, then the first one is taken out in flashback. So take a picture of the sequence, and then take out the first one.

sql:select top 1 username from tablename order by username desc ;The above is to take out the first record, if you want to take out the last one: sql:

select top 1 username from tablename order by username asc;

The database projects the data by behavioral units, querying, arising, and so on.

If you want to subtract the data of two rows of the same table, you should make the table self-join, the connection condition is the correspondence you said, and then after joining, it will become one row, and the value will become two columns, and you can calculate.

Example: select

from my table a,my table bwhere correspondence;

The requirements are not clear, and the general query statement:

select field 1, field 2 from table where 1=1 and [other condition 1] and [other condition 2].

You can describe your needs and explain them clearly before you know if I will.

1. The serial number must be in the consumption table, and each number is different, if the number field is ID.

2. The following is an example**, please understand and use it. The general idea is always like that.

declare @ls1number numeric(16, 2) - Last balance used.

declare @ls2number numeric(16, 2) - for this consumption.

Get last balance --

select top2 @ls1number=Balance form consumption table.

where Consumer Number = Specified Number.

order by id

Get this credit.

select top2 @ls2number=Consumption amount form Consumption table.

where Consumer Number = Specified Number.

order by id desc

Get the balance this time.

select @ls1number - ls2number as the current balance.

Update this balance.

update Consumption table set balance = (@ls1number - ls2number).

where id in (

select top1 @ls2number=Consumption amount form consumption table.

where Consumer Number = Specified Number.

order by id desc)

Select Column 1 + Column 2 + Column 3 ......Column n as total

from table.

Add all the columns you want to calculate, and you're good to go.

(select id, value from table).

unionselect 0 as id,sum(numeric value) as sum from table).

It seems that all the fields can be added up.

select (top 1 real point) - (select real point from table b where b.)License plate = aLicense plate and b

time from table a where'License plates' order by a.Time desc

The general idea, the specific grammar is tested by yourself.

-Recursion. create or replace function recursion func(in number) return number is

v_sum number;

v Debit amount number;

v Credit amount number;

beginselect debit amount, credit amount into v debit amount, v credit amount from table name;

if in <= 1 then

v sum := opening amount;

elsev sum := recursion func(in ordinal number -1) +v debit amount - v credit amount;

end if;

return v_sum;

end;- Execute the above, and then execute the following, and modify it yourself.

select sequence number, debit amount, direction, credit amount, recursion func (sequence number) from table name

SQL does not directly support this incremental summation method, you can build a temporary table, and calculate the balance of each row through the cursor (in fact, it is the sum (debit amount - credit amount) + opening balance of all historical data before the current record row), and then associate it with your table to get the result you want.

Select Account Code, SUM (Balance).

from Statistical Account Balance Table.

where len (subject code) = 3 and direction ='Borrow'

group by Account code.