I want to put in the picture above and the balance in the SQL statement is used directly written out
The balance field is increased when the query table fields, other fields are directly fetch the value of the
Calculation is: O2=O1 + M2 + N2
O3=O2 + M3 + N3
Behind and so on
When the first line has no balance of the previous period how to deal with this part?
If write a function to how to write?
If don't have to write a function to how to write?

CodePudding user response:

With the data (_index add_amount, red_amount) as
(
Union all select 1, 0, - 3080
Select 2, 3003, 0 union all
Union all select 3, 0, - 154
Union all select 4, 77, 0
40 union all select 5, 0,
Union all select 6, 0, - 1580
Select 7, 1540, 0
)
- select * from data
Select data. *, rest=(select sum (add_amount) + sum (red_amount) from data data2 where data2. _index & lt;=data. _index)
From the data

Words, you are the numeration method also is not very reasonable, how is in a column, there are negative? If a negative number means the opposite, it is suggested that build another column

CodePudding user response:

Is negative is not important, I add column and column reduction is calculate by addition of, so there is no what effect

CodePudding user response:

Can increase the amount to reduce the amount of merger into a line, then the sum (a) the balance of getting what you want

CodePudding user response:

Using a recursive with data and can calculate 100 layers,

Directly using a recursive only 32 layer; Actually cannot solve practical problems,
There is a problem, like you this kind of problem, the best the data directly to the post in the form of text, you can go to try to help you, otherwise, because most of the data, which no one is willing to help you to play the most convenient and tested,

CodePudding user response:

There are two methods, using a cursor, or use the cross table

CodePudding user response:

M, N, O data posted

CodePudding user response:

Serial number is according to the serial number can be accumulated, it has no serial number will add a serial number

 DECLARE @ t TABLE (serial number INT the NOT NULL, adding a DECIMAL (10, 2) NOT NULL, reduce a DECIMAL (10, 2) NOT NULL) 
INSERT the @ t (serial number, increase and decrease) 
VALUES (57, 1405-9736), (2611, 85-89882), (55, 3909-59306), 
(56, 4656-79207), (5226, 74-21292), (36, 6845-72462), 
(49, 7728-68889), the (52, 8583-42120), (6, 9383-55301), 
(3, 10728-27803), (28, 11807-34710), (12582, 63-82854), 
(59, 13329-85673), (37, 14514-93619), (17, 15386-96284) 
; WITH t AS (SELECT *, CONVERT (a DECIMAL (10, 2), increase + decrease) balance the FROM @ t WHERE the serial number=1 
UNION ALL 
The SELECT t2. *, 
CONVERT (a DECIMAL (10, 2), t2. Add + t2. Reduce + t. balance) 
The FROM @ t t2 
INNER JOIN t ON t2) serial number=t) serial number + 1) 
SELECT * FROM t; 

CodePudding user response:

Use cursor processing is simple, but speed is not fast