uj5u.com熱心網友回復：
CREATE TABLE #A (
ID VARCHAR(20),
PRICE INT,
NUM INT
)
INSERT INTO #A VALUES ('蘋果',10,15)
INSERT INTO #A VALUES ('香蕉',15,7)
INSERT INTO #A VALUES ('獼猴桃',3,12)
INSERT INTO #A VALUES ('獼猴桃',6,11)
INSERT INTO #A VALUES ('香蕉',8,9)
INSERT INTO #A VALUES ('蘋果',22,35)
INSERT INTO #A VALUES ('蘋果',34,27)
INSERT INTO #A VALUES ('香梨',88,12)
;WITH CTE1 AS(
SELECT A.ID,SUM(A.NUM) 總數,(SELECT SUM(B.NUM*B.PRICE) FROM #A B WHERE B.ID = A.ID) 總金額,
ROW_NUMBER() OVER(ORDER BY ID) RN FROM #A A GROUP BY A.ID
),
CTE2 AS (
SELECT A.ID,5000 總賬,A.總數,A.總金額,5000-A.總金額 余額,A.RN FROM CTE1 A WHERE A.RN = 1
UNION ALL
SELECT A.ID,B.余額,A.總數,A.總金額,B.余額-A.總金額,A.RN FROM CTE1 A,CTE2 B WHERE A.RN = B.RN+1
)
SELECT * FROM CTE2
DROP TABLE #A
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/264000.html
標籤：疑難問題