I have a water table to insert multiple records
Assume that the user balance of 1000
Multiple records in the table is another come out as A
A
The amount of ID (on the)
1 100
2 200
3 300

Insert table
The amount of ID (on the) balance
1 100 900
2 200 700
3 300 400

CodePudding user response:

 
IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
The AMOUNT INT) 

INSERT INTO # T 
100 UNION ALL SELECT 
200 UNION ALL SELECT 
SELECT 300 

GO 

DECLARE @ PAYMENT INT 

The SET @ PAYMENT=1000 

SELECT A. *, @ PAYMENT - SUBTOTAL AS BALANCE FROM # T A 
OUTER APPLY (SELECT SUM (AMOUNT) AS SUBTOTAL FROM # T WHERE ID<=Anderson, D) AS B 

CodePudding user response:

This calculation above, large amount of data, speed is slow

CodePudding user response:

The second floor, there is a better way?

CodePudding user response:

If you are this user beginning balance is the same, every time you are inserting new data in the table, put this value is calculated and saved into, after check when need not real-time computing,

CodePudding user response:

What you need is to insert a new record automatically calculate the balance, but the balance is not batch can be calculated, but on the basis of a result to calculate, this kind of demand, in fact, in violation of the database "row order irrelevant" principle and "rows of data are independent of each other" principle, so the native SQL statement does not support this calculation, seemingly in addition to using a cursor, or use a similar 1 # reply, there is no other way, is on record and be parallel, so impossible to fast,

CodePudding user response:

reference 4 floor RINK_1 response:

if you are this user beginning balance is the same, every time you are inserting new data in the table, put this value is calculated and saved into, after check when need not real-time computing,

Didn't see you is to insert the new data to calculate, if so, that really like # 5,

CodePudding user response:

 
- sql2012 + 
IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
The AMOUNT INT) 

INSERT INTO # T 
100 UNION ALL SELECT 
200 UNION ALL SELECT 
SELECT 300 

GO 
DECLARE @ PAYMENT INT 
The SET @ PAYMENT=1000 
SELECT *, @ PAYMENT - SUM (AMOUNT) OVER (ORDER BY ID) AS the balance of the FROM # T 

CodePudding user response:

Also can use the cte, using a recursive sequence added on balance, but need to ensure that data will not be tampered with and delete
In addition, also is not each time, calculation of the whole table data already has data, only take the last line based on line of balance