在 SQL Server 中使用 LAG() 和 LEAD() 函数时是否可以忽略 NULL 值? [英] Is it possible to ignore NULL values when using LAG() and LEAD() functions in SQL Server?

问题描述

如你所知 LAG() &LEAD() 分析函数从同一结果集中的上一行和下一行访问数据，而无需使用自联接.但是在访问 NOT NULL 值之前是否可以忽略 NULL 值?

As you know the LAG() & LEAD() analytic functions access data from a previous and next row in the same result set without the use of a self-join. But is it possible to ignore NULL values until access to a NOT NULL value?

推荐答案

可以使用窗口函数.阅读 Itzik Ben-Gan 的这篇文章更多详情.

Its possible using window functions. Have a read of this article by Itzik Ben-Gan for more details.

在下面的代码中，cte 获取最新的 NOT NULL id 值，然后下一个选择获取实际的列值.本示例使用 LAG.例如.

In the code below, the cte is getting the most recent NOT NULL id value, then the next select gets the actual column value. This example uses LAG. eg.

-- DDL for T1
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
col1 INT NULL
);

-- Small set of sample data
TRUNCATE TABLE dbo.T1;

INSERT INTO dbo.T1(id, col1) VALUES
( 2, NULL),
( 3,   10),
( 5,   -1),
( 7, NULL),
(11, NULL),
(13,  -12),
(17, NULL),
(19, NULL),
(23, 1759);

;WITH C AS
(
SELECT
    id, 
    col1, 
    MAX(CASE WHEN col1 IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp
FROM dbo.T1
)
SELECT
    id, 
    col1, 
    (SELECT col1 FROM dbo.T1 WHERE id = grp) lastval    
FROM C;

这篇关于在 SQL Server 中使用 LAG() 和 LEAD() 函数时是否可以忽略 NULL 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！