視窗函式累積聚合

我通過總結一些列來計算累積。代碼正在運行。但我只想在第一行包含一個額外的變數。然后計算錯誤。

我想在第 1 行中做什么：

SUM(@devir   netTakas - ToplamCikis   YeniKrediKullan?m? - Islem)

除了第 1 行，我想做什么：

SUM(netTakas - ToplamCikis   YeniKrediKullan?m? - Islem)

當我用 case 這樣做時，得到了錯誤的結果。

SQL 代碼：

declare @devir float = 1308973;
    WITH asilTablo AS(
        select * from #tmpbrut1
        union
        select * from #tmpbrut
    )
    SELECT 
        *,
        CASE WHEN ROW_NUMBER() OVER(order by takasTarihi) >= 2 then
        SUM(netTakas - ToplamCikis   YeniKrediKullan?m? - Islem) OVER (ORDER BY takasTarihi ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        else SUM(@devir   netTakas - ToplamCikis   YeniKrediKullan?m? - Islem)  OVER (ORDER BY takasTarihi ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end AS [Kümülatif Toplam]
    FROM asilTablo

因此，例如在第 1 行，它將執行以下操作：

@devir netTakas-ToplamCikis YeniKredKullan?m?-Islem = Kümülatif Toplam

在第 1 行以外的地方，它會這樣做：

Kümülatif Toplam   netTakas-ToplamCikis YeniKredKullan?m?-Islem

@devir = 10 第一行操作：

@devir   netTakas - ToplamCikis   YeniKrediKullan?m? - Islem

另一行的操作：

netTakas - ToplamCikis   YeniKrediKullan?m? - Islem

第一行變數和列的運算，第1行列與下一行“累計”列的總和

CREATE TABLE #Tmp1(
     tarih date,
    netTakas int,
    ToplamCikis int,
    YeniKrediKullan?m? int,
    Islem int
)

CREATE TABLE #Tmp2(
     tarih date,
    netTakas int,
    ToplamCikis int,
    YeniKrediKullan?m? int,
    Islem int
)

insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211227', 125, 34, 57, 347)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211228', 135, 34, 587, 3624)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211229', 125, 347, 577, 34)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211230', 175, 344, 597, 374)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211231', 1875, 364, 587, 38684)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20220103', 1295, 354, 575, 344)
insert into #Tmp1 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20220104', 1285, 344, 547, 344)

insert into #Tmp2 (tarih, netTakas, ToplamCikis, YeniKrediKullan?m?, Islem) VALUES ('20211226', 12453485, 3444, 5447, 34544)

declare @devir float = 1308973;
WITH asilTablo AS
(
    SELECT *
    FROM #Tmp1
union
Select *
from #Tmp2
)
SELECT  
    tarih,
    netTakas,
    ToplamCikis,
    YeniKrediKullan?m?,
    Islem,
    CASE WHEN ROW_NUMBER() OVER(order by tarih) >= 2 then SUM(netTakas - ToplamCikis   YeniKrediKullan?m? - Islem) OVER (ORDER BY tarih ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
    else  SUM(@devir   netTakas - ToplamCikis   YeniKrediKullan?m? - Islem) OVER (ORDER BY tarih ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  end  AS [Kümülatif Toplam]
FROM
    asilTablo

uj5u.com熱心網友回復：

用于ROW_NUMBER()內部查詢。在外部查詢中檢查何時ROW_NUMBER() =1包含@devir在計算中

SELECT
    SUM (CASE WHEN RN = 1 THEN @devir ELSE 0 END
           netTakas - ToplamCikis   YeniKrediKullan?m? - Islem) 
    OVER (ORDER BY tarih)
FROM
(
    SELECT *, RN = ROW_NUMBER() OVER(order by tarih)
    FROM   asilTablo
) a

標籤：sql sql-server 累积和 开窗

上一篇：選擇時計算百分比-T-SQL

下一篇：SQLServer日期列不一致