How to use SQL server expressions on a field value is equal to the line of field value addition to t-CodePudding

As shown in figure, how to use how to use SQL server expressions, automatic calculation, when new data column 4 is the first row of data is empty

CodePudding user response:

 

- a moderator to statements 

- test data 
If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [item_no], [AMOUNT_OF_PAGED] int) 
Insert # T 
Select 1, union all 
Union all select 2 and 5 
Union all select 3 and 5 
Select 4, 8 union all 
Select 5, 11 union all 
Select 6 union all 16 
The select 7, 1 union all 
8, 24 union all select 
The select 9, 11 union all 
The select 10, 14 
Go 
- the end of the test data 
; WITH cte AS (
Select *, 1 AS PAGE_NO from # T WHERE item_no=1 
UNION ALL 
SELECT # T *, cte. PAGE_NO + cte. AMOUNT_OF_PAGED FROM # T JOIN cte ON cte, item_no + 1=# T.i tem_no 
) 
SELECT * FROM cte

CodePudding user response:

Lead and lag these two functions, that is, in order to solve the problem of the building Lord,

CodePudding user response:

The

reference 1/f, one tree forest _ response:

 

- a moderator to statements 

- test data 
If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [item_no], [AMOUNT_OF_PAGED] int) 
Insert # T 
Select 1, union all 
Union all select 2 and 5 
Union all select 3 and 5 
Select 4, 8 union all 
Select 5, 11 union all 
Select 6 union all 16 
The select 7, 1 union all 
8, 24 union all select 
The select 9, 11 union all 
The select 10, 14 
Go 
- the end of the test data 
; WITH cte AS (
Select *, 1 AS PAGE_NO from # T WHERE item_no=1 
UNION ALL 
SELECT # T *, cte. PAGE_NO + cte. AMOUNT_OF_PAGED FROM # T JOIN cte ON cte, item_no + 1=# T.i tem_no 
) 
SELECT * FROM cte

Test and show the icon does not match with me

CodePudding user response:

reference JJJJK0 reply: 3/f

Quote: reference 1/f, one tree forest _ response:

 

- a moderator to statements 

- test data 
If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (int [item_no], [AMOUNT_OF_PAGED] int) 
Insert # T 
Select 1, union all 
Union all select 2 and 5 
Union all select 3 and 5 
Select 4, 8 union all 
Select 5, 11 union all 
Select 6 union all 16 
The select 7, 1 union all 
8, 24 union all select 
The select 9, 11 union all 
The select 10, 14 
Go 
- the end of the test data 
; WITH cte AS (
Select *, 1 AS PAGE_NO from # T WHERE item_no=1 
UNION ALL 
SELECT # T *, cte. PAGE_NO + cte. AMOUNT_OF_PAGED FROM # T JOIN cte ON cte, item_no + 1=# T.i tem_no 
) 
SELECT * FROM cte

Test and show the icon does not match with me

 


- try to change the rules of the game 
; WITH cte AS (
Select *, 0 AS PAGE_NO from # T WHERE item_no=1 
UNION ALL 
SELECT # T *, cte. Item_no + cte. AMOUNT_OF_PAGED FROM # T JOIN cte ON cte, item_no + 1=# T.i tem_no 
) 
SELECT * FROM cte