我有以下財務資料，我需要顯示截至日期的余額。我使用回圈使用了以下邏輯，但我想使用單個查詢在沒有回圈或游標的情況下實作相同的目標。對此的任何想法都會有所幫助。
DROP TABLE IF EXISTS #Transact;
CREATE TABLE #Transact
(
dt DATE,
Transact VARCHAR(1),
Amount INT,
Balance INT
)
INSERT INTO #Transact
(
dt,
Transact,
Amount
)
SELECT *
FROM
(
VALUES
('01-01-2022', 'C', 1000),
('02-01-2022', 'C', 1000),
('03-01-2022', 'D', 1000),
('04-01-2022', 'C', 2000),
('05-01-2022', 'C', 2000),
('06-01-2022', 'D', 2000),
('07-01-2022', 'D', 2000),
('08-01-2022', 'C', 3000),
('09-01-2022', 'C', 1500),
('10-01-2022', 'D', 1500),
('11-01-2022', 'D', 1500),
('12-01-2022', 'C', 1500)
) VTE (DT, Transact, Amount);
declare @interval date = '01-01-2022',
@balance int
WHILE (@interval <= (select max(dt) FROM #Transact))
BEGIN
;WITH CTE
AS (SELECT *,
LAG(Balance) Over (Order by dt) AS PreBalance
FROM #Transact
)
UPDATE CTE
SET Balance = CASE
WHEN Transact = 'C' THEN
Amount ISNULL(PreBalance, 0)
ELSE
ISNULL(PreBalance, 0) - Amount
END
WHERE dt = @interval
SET @interval = DATEADD(DAY, 1, @interval)
SELECT @balance = Balance
from #Transact
where dt = @interval
END
SELECT * FROM #Transact;
我有非常大量的此類資料，我的要求是避免使用游標或回圈進行此計算。
uj5u.com熱心網友回復：
我認為這是一個總和，所以你需要一個視窗SUM()和一個CASE運算式：
SELECT
dt, Transact, Amount,
SUM(
CASE
WHEN Transact = 'C' THEN Amount
WHEN Transact = 'D' THEN -Amount
ELSE 0
END) OVER (ORDER BY dt) AS Balance
FROM #Transact
uj5u.com熱心網友回復：
您是否嘗試過這樣的簡單查詢：
SELECT
(
SELECT SUM(Amount) FROM #Transact WHERE Transact = 'C' and dt <= @interval
)
-
(
SELECT SUM(Amount) FROM #Transact WHERE Transact = 'D' and dt <= @interval
)
as Balance
基本上，您只需計算所有貸方并減去所有借方，直到該日期。
或者，正如@Zhorov 所建議的：
SELECT SUM
(
CASE
WHEN Transact = 'C' THEN Amount
WHEN Transact = 'D' THEN -Amount
ELSE 0
END
) as Balance
FROM #Transact WHERE dt <= @interval
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/477199.html
上一篇：SQLCE回傳時間范圍的總和值