时间戳

首页 > 解决方案 > 获取缺失日期的缺失记录并使用之前的非 Null 值更新值

sql - 获取缺失日期的缺失记录并使用之前的非 Null 值更新值

问题描述

我有下表，其中教师每天更新数据，但有些教师不会每天更新记录。我需要找到丢失的日期并在 Val 和 TotalValue 字段中添加以前的 NOT NULL 值。我有日期表，我确实离开了日期表，但无法更新非空记录。1 月 2 日和 1 月 3 日缺少行，我需要在结果中添加行

DECLARE @Table TABLE(
        ID INT,
        ClassID VARCHAR(10),
        Val INT,
        TotalValue INT,
        UpdatedDateTime DATETIME2

)

INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 20, 100, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'ZXCV5', 50, 70, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'GHT09', 40, 40, '2020/1/1 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 70, 100, '2020/1/2 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'GHT09', 38, 40, '2020/1/2 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'AB475', 20, 100, '2020/1/3 14:00:20'
INSERT INTO @Table (ID, ClassID, Val,TotalValue, UpdatedDateTime) SELECT 1, 'ZXCV5', 20, 70, '2020/1/3 14:00:20'

SELECT * FROM @Table

ID  ClassID Val TotalValue  UpdatedDateTime
1   AB475   20  100 2020-01-01 14:00:20.0000000
1   ZXCV5   50  70  2020-01-01 14:00:20.0000000
1   GHT09   40  40  2020-01-01 14:00:20.0000000
1   AB475   70  100 2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-02 14:00:20.0000000
1   AB475   20  100 2020-01-03 14:00:20.0000000
1   ZXCV5   20  70  2020-01-03 14:00:20.0000000

SELECT *
FROM [DATE]
LEFT JOIN @Table
    ON [DATE].[DATE] = CAST([@Table].UpdatedDateTime AS DATE)

最终结果应该是

ID  ClassID Val TotalValue  UpdatedDateTime
1   AB475   20  100 2020-01-01 14:00:20.0000000
1   ZXCV5   50  70  2020-01-01 14:00:20.0000000
1   GHT09   40  40  2020-01-01 14:00:20.0000000
1   AB475   70  100 2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-02 14:00:20.0000000
1   AB475   20  100 2020-01-03 14:00:20.0000000
1   ZXCV5   20  70  2020-01-03 14:00:20.0000000
1   ZXCV5   50  70  2020-01-02 14:00:20.0000000
1   GHT09   38  40  2020-01-03 14:00:20.0000000

标签： sqlsql-servertsqlselect

解决方案

我将使用cross join两个select distinct查询来生成教师和日期的所有可能组合，然后用于cross apply获取缺失的信息：

select c.ID, c.ClassID, t.Val, t.TotalValue, d.UpdatedDateTime
from (select distinct ID, ClassID from @table) c
cross join (select distinct UpdatedDateTime from @table) d
cross apply (
    select top 1 *
    from @table t
    where t.ClassID = c.ClassID and t.UpdatedDateTime <= d.UpdatedDateTime
    order by t.UpdatedDateTime desc
) t
order by c.ID, c.classId, d.UpdatedDateTime

DB Fiddle 上的演示：

身份证 | 类ID | 瓦尔 | 总价值 | 更新日期时间            
-: | :-------- | --: | ---------: | :----------------------------
 1 | AB475 | 20 | 100 | 2020-01-01 14:00:20.0000000
 1 | AB475 | 70 | 100 | 2020-01-02 14:00:20.0000000
 1 | AB475 | 20 | 100 | 2020-01-03 14:00:20.0000000
 1 | GHT09 | 40 | 40 | 2020-01-01 14:00:20.0000000
 1 | GHT09 | 38 | 40 | 2020-01-02 14:00:20.0000000
 1 | GHT09 | 38 | 40 | 2020-01-03 14:00:20.0000000
 1 | ZXCV5 | 50 | 70 | 2020-01-01 14:00:20.0000000
 1 | ZXCV5 | 50 | 70 | 2020-01-02 14:00:20.0000000
 1 | ZXCV5 | 20 | 70 | 2020-01-03 14:00:20.0000000

推荐阅读