How SQL server through the data in table 2 for the third four or five items data in table 1?-CodePudding

CodePudding user response:

 
The CREATE TABLE # A (
ID VARCHAR (20), 
PRICE INT, 
NUM INT 
) 

# INSERT INTO A VALUES (' apple ', 10, 15) 
# INSERT INTO A VALUES (' banana ', 15, 7) 
# INSERT INTO A VALUES (' kiwi ', 3, 12) 
# INSERT INTO A VALUES (' kiwi ', 6, 11) 
# INSERT INTO A VALUES (' banana ', 8, 9) 
# INSERT INTO A VALUES (' apple ', 22, 35) 
# INSERT INTO A VALUES (' apple ', 34, 27) 
# INSERT INTO A VALUES (' fragrant pear, 88, 12) 

; WITH CTE1 AS (
SELECT Anderson D, the SUM (A.N UM), total (SELECT SUM (B.N UM * p. RICE) FROM # B WHERE A B.I D=Anderson, D), the total amount 
ROW_NUMBER () OVER (ORDER BY ID) RN # FROM aa GROUP BY Anderson D 
), 
CTE2 AS (
SELECT Anderson, D, 5000, general ledger, a. total, a. total amount, 5000 - A. Total amount of balance, A.R N FROM CTE1 WHERE A A.R N=1 
UNION ALL 
Balance of the SELECT Anderson, D, B., a. total, a. total amount, balance - a. b. total amount, A.R N FROM CTE1 A, CTE2 B WHERE A.R N=B.R N + 1 
) 
SELECT * FROM CTE2 

DROP TABLE # A