将上述行中的非空值放入具有空值的当前行。 [英] Put not null values from above rows to current rows having null values .

问题描述

CustomerID FirstName CreateDate

1 Jonah 2011-09-01 00：00：00.000

2 Jonathan NULL

3 Leno 2014-05 -05 00：00：00.000

4 James NULL

5 Tom 2018-05-01 00：00：00.000

6 Harry NULL

7 Ricky NULL

8 Katheriene NULL



问题：将日期列上的空值替换为非空值以上。

我用例声明得到低于结果但速度很慢。

预期输出：

CreateDate CreateDate

2011-09-01 00：00：00.000 2011-09-01 00：00：00.000

NULL 2011-09-01 00：00：00.000

2014-05-05 00：00：00.000 2014-05-05 00：00：00.000

NULL 2014-05-05 00：00：00.000

2018-05 -01 00：00：00.000 2018-05-01 00：00：00.000

NULL 2018-05-01 00：00：00.000

NULL 2018-05-01 00：00：00.000

NULL 2018-05-01 00：00：00.000



我尝试过：



SELECT main.CreateDate，创建日期时的情况IS NULL

THEN（SELECT TOP 1 sub.CreateDate FROM dbo.Customer sub WHERE sub.CustomerID< = main.CustomerID

order by sub.CreateDate desc）

ELSE main.CreateDate

END AS CreateDate

来自dbo.Customer main;

解决方案

< blockquote>假设SQL 2012或更高版本，来自Dwain Camps的这种技术可能有所帮助：

  WITH  cteCount  As  
（
  SELECT  
 CustomerID，
 FirstName，
 CreateDate，
 COUNT（CreateDate） OVER （ ORDER   BY  CustomerID） As  C 
  FROM  
 dbo.Customer 
），
 cteRN < span class =code-keyword> As  
（
  SELECT  
 CustomerID，
 FirstName， 
 CreateDate，
 ROW_NUMBER（） OVER （ PARTITION   BY  C  ORDER   BY  CustomerID） -   1  作为 RN 
  FROM  
 cteCount 
）
  SELECT  
 CustomerID，
 FirstName，
 CreateDate，
  CASE  
  WHEN  CreateDate   不  Null   THEN  CreateDate 
  ELSE  LAG（CreateDate，RN） OVER （ ORDER   BY  CustomerID，CreateDate）
  END  作为 CreateDate 
  FROM  
 cteRN 
  ORDER   BY  
 CustomerID 
; 

使用T-SQL窗口框填充缺失值 - 简单对话 [ ^ ]

CustomerID FirstName CreateDate
1 Jonah 2011-09-01 00:00:00.000
2 Jonathan NULL
3 Leno 2014-05-05 00:00:00.000
4 James NULL
5 Tom 2018-05-01 00:00:00.000
6 Harry NULL
7 Ricky NULL
8 Katheriene NULL

Problem : Replace null values on date column from not null value of above.
I am use case statement to get below result but its very slow.
Expected output:
CreateDate CreateDate
2011-09-01 00:00:00.000 2011-09-01 00:00:00.000
NULL 2011-09-01 00:00:00.000
2014-05-05 00:00:00.000 2014-05-05 00:00:00.000
NULL 2014-05-05 00:00:00.000
2018-05-01 00:00:00.000 2018-05-01 00:00:00.000
NULL 2018-05-01 00:00:00.000
NULL 2018-05-01 00:00:00.000
NULL 2018-05-01 00:00:00.000

What I have tried:

SELECT main.CreateDate,CASE WHEN CreateDate IS NULL
THEN (SELECT TOP 1 sub.CreateDate FROM dbo.Customer sub WHERE sub.CustomerID <= main.CustomerID
order by sub.CreateDate desc )
ELSE main.CreateDate
END AS CreateDate
FROM dbo.Customer main;

解决方案

Assuming SQL 2012 or higher, this technique from Dwain Camps might help:

WITH cteCount As
(
    SELECT
        CustomerID,
        FirstName,
        CreateDate,
        COUNT(CreateDate) OVER (ORDER BY CustomerID) As C
    FROM
        dbo.Customer
),
cteRN As
(
    SELECT
        CustomerID,
        FirstName,
        CreateDate,
        ROW_NUMBER() OVER (PARTITION BY C ORDER BY CustomerID) - 1 As RN
    FROM
        cteCount
)
SELECT
    CustomerID,
    FirstName,
    CreateDate,
    CASE
        WHEN CreateDate Is Not Null THEN CreateDate
        ELSE LAG(CreateDate, RN) OVER (ORDER BY CustomerID, CreateDate)
    END As CreateDate
FROM
    cteRN
ORDER BY
    CustomerID
;

Filling In Missing Values Using the T-SQL Window Frame - Simple Talk[^]

这篇关于将上述行中的非空值放入具有空值的当前行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！