34,838
社区成员




--sql2012+
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(ID INT IDENTITY(1,1),
AMOUNT INT)
INSERT INTO #T
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300
GO
DECLARE @PAYMENT INT
SET @PAYMENT=1000
SELECT *,@PAYMENT-SUM(AMOUNT) OVER(ORDER BY ID) AS 余额 FROM #T
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(ID INT IDENTITY(1,1),
AMOUNT INT)
INSERT INTO #T
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300
GO
DECLARE @PAYMENT INT
SET @PAYMENT=1000
SELECT A.*,@PAYMENT-SUBTOTAL AS BALANCE FROM #T A
OUTER APPLY (SELECT SUM(AMOUNT) AS SUBTOTAL FROM #T WHERE ID<=A.ID) AS B