在 SQL Server 中的计算列上使用 LAG 函数

小编典典

在 SQL Server 中的计算列上使用 LAG 函数

sql

我想根据条件计算当月的开/关金额。我正在使用相同的计算列。就我而言，上个月的收盘是下个月的开盘，所以我尝试使用 LAG 功能。但无法取得成果。

在第一个月，OpeningAmt 将是 YearAmt

我的表结构如下

emp_code	year1	year2	col_code	paidin	YearAmt	Increment	Used
330	202204	202303	a3	202204	15000.00	1250.00	100
330	202204	202303	a3	202205	15000.00	1250.00	100
330	202204	202303	a3	202206	15000.00	1250.00	100
330	202204	202303	a3	202207	15000.00	1250.00	100
330	202204	202303	a3	202208	15000.00	1250.00	100

我的预期输出如下

emp_code	year1	year2	col_code	paidin	YearAmt	Increment	Used	OpeningAmt	ClosingAmt
330	202204	202303	a3	202204	15000.00	1250.00	100	15000.00	16150.00
330	202204	202303	a3	202205	15000.00	1250.00	100	16150.00	17300.00
330	202204	202303	a3	202206	15000.00	1250.00	100	17300.00	18450.00
330	202204	202303	a3	202207	15000.00	1250.00	100	18450.00	19600.00
330	202204	202303	a3	202208	15000.00	1250.00	100	19600.00	20750.00

尝试查询：

SELECT *,

CASE WHEN year1 = MonthYear THEN NULL ELSE 
LAG(StartMonthClosing,1) OVER (ORDER BY emp_code, col_code, MonthYear) 
END OtherMonthOpening

FROM (
SELECT emp_code, year1,year2,col_code, MonthYear ,YearAmt,  Increment,  Used,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt ELSE NULL END StartMonthOpening,
CASE WHEN year1 = MySalaryTable.paidin THEN YearAmt + Increment - Used ELSE NULL END StartMonthClosing
FROM MyreimTable
INNER JOIN MySalaryTable ON MyreimTable.emp_code = MySalaryTable.emp_code
) 
AS P2

阅读 119

2022-07-21

共1个答案

小编典典

由于您需要来自所有先前行的数据，而不仅仅是前一行，因此您需要使用SUM() OVER()而不是LAG() OVER(). 以下从您的示例数据中给出了所需的结果：

SELECT  t.emp_code, 
        t.year1, 
        t.year2, 
        t.col_code, 
        t.paidin, 
        t.YearAmt, 
        t.Increment, 
        t.Used,
        OpeningAmt = SUM(CASE WHEN t.year1 = t.paidin THEN t.YearAmt ELSE 0 END + t.Increment - t.Used) 
                            OVER(PARTITION BY t.Emp_code, t.col_code
                                ORDER BY t.paidin ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        ClosingAmt = SUM(CASE WHEN t.year1 = t.paidin THEN t.YearAmt ELSE 0 END + t.Increment - t.Used) 
                        OVER(PARTITION BY t.Emp_code, t.col_code ORDER BY t.paidin)
FROM    dbo.YourTable AS t;

db<>fiddle 上的示例

2022-07-21