代码之家 › 专栏 › 技术社区 › Anu Viswan

行不属于子查询时的最后一个非空值

tsql sql-server sql

Anu Viswan · 技术社区 · 4 年前

Id  Value
1   Sample 1
2   Sample 2
3   NULL
4   NULL
5   NULL
6   Value 4
7   NULL
8   Value5
9   NULL
10  value6

定义为

CREATE TABLE [dbo].[Demo](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](max) SPARSE  NULL,
 CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

在这个表中,值字段(根据业务逻辑)应该只有在发生更改时才会更改。如果该值没有改变,它将由NULL表示(意味着它具有最后一个非NULL值)。

Link

  
WITH C AS
(
  SELECT id, [Value],
    MAX( CASE WHEN [Value] IS NOT NULL THEN id END )
  OVER( ORDER BY id
        ROWS UNBOUNDED PRECEDING ) AS grp
  FROM demo
)
SELECT id, [value],
  MAX([value]) OVER( PARTITION BY grp
          ORDER BY id
          ROWS UNBOUNDED PRECEDING ) AS LastValue
FROM C

id  value   LastValue
1   Sample 1    Sample 1
2   Sample 2    Sample 2
3   NULL        Sample 2
4   NULL        Sample 2
5   NULL        Sample 2
6   Value 4     Value 4
7   NULL        Value 4
8   Value5      Value5
9   NULL        Value5
10  value6      value6

如屏幕截图所示,只要我检索整个表,它就可以正常工作。然而,当我只检索一个子集时,这就成了一个问题。例如,如果我只需要在以下位置获取信息 Id>2.

id  value   LastValue
3   NULL    NULL
4   NULL    NULL
5   NULL    NULL
6   Value 4 Value 4
7   NULL    Value 4
8   Value5  Value5
9   NULL    Value5
10  value6  value6

如前所述,第一行(ID=3)的值为“NULL”。但是,在数据库中,它有一个值 Sample 2 来自行(ID=2),由于我们添加的条件,该行不是查询的一部分。

是否有人能建议一种方法,即使子查询不包含特定的行,我仍然可以获取最后一个非值的值(该行不必在前一行,它应该是最后一个非空值的行)?我对上述场景的预期结果如下。

id  value   LastValue
3   NULL    Sample 2
4   NULL    Sample 2
5   NULL    Sample 2
6   Value 4 Value 4
7   NULL    Value 4
8   Value5  Value5
9   NULL    Value5
10  value6  value6

在实际场景中,可能有多个类似于值的稀疏/可空列,对于每个列,我需要获取最后一个已知的值。但是,为了简单起见,我们使用单列进行了示例。

PS:但是,表中的第一个为空。从业务逻辑的角度来看,这被认为是异常值。

1 回复 | 直到 4 年前

Dale K 4 年前

只需使用子查询来提取所选范围之前的第一个非空值,然后将其用于第一个 Grp 如果为空。

WITH C AS
(
    SELECT Id, [Value]
        , MAX(CASE WHEN [Value] IS NOT NULL THEN Id END) OVER (ORDER BY Id ROWS UNBOUNDED PRECEDING) AS Grp
        -- Find the first value prior to our resultset in case we start from null
        , (SELECT TOP 1 [Value] FROM #demo D1 WHERE D1.Id < D.Id AND [Value] IS NOT NULL ORDER BY D1.Id DESC) InitialValue
    FROM #demo D
    WHERE Id > 2
)
SELECT Id, [value], Grp, InitialValue
    , CASE WHEN Grp IS NULL THEN InitialValue ELSE MAX([value]) OVER (PARTITION BY Grp ORDER BY Id ROWS UNBOUNDED PRECEDING) END AS LastValue
FROM C;

身份证件
3.	无效的	样本2
	无效的
5.		样本2
6.	价值4	价值4
7.	无效的	价值4
	价值5
9	无效的	价值5
	价值6