时间戳

首页 > 解决方案 > 查询更改为单列时态表

sql-server - 查询更改为单列时态表

问题描述

我有一个包含以下数据的 SQL Server 时态表：

ID  ValidFrom       ValidTo         MyValue
23  7/7/19 13:51    7/7/19 13:51    0
23  7/7/19 13:51    9/9/19 11:22    0
23  9/9/19 11:22    9/9/19 11:23    0
23  9/9/19 11:23    5/14/20 23:02   0
23  5/14/20 23:02   5/16/20 20:02   0
23  5/16/20 20:02   5/16/20 23:53   0
23  5/16/20 23:53   5/16/20 23:58   0
23  5/16/20 23:58   5/16/20 23:58   0
23  5/16/20 23:58   5/16/20 23:59   0
23  5/16/20 23:59   5/17/20 0:16    0
23  5/17/20 0:16    5/17/20 1:47    0
23  5/17/20 1:47    5/17/20 1:48    0
23  5/17/20 1:48    5/20/20 16:52   0
23  5/20/20 16:52   5/20/20 16:52   0
23  5/20/20 16:52   8/22/20 0:22    0
23  8/22/20 0:22    9/3/20 20:22    0
23  9/3/20 20:22    9/3/20 20:23    0
23  9/3/20 20:23    12/31/99 0:00   6

我想执行一个查询，所以我只得到'MyValue'的变化点，如下所示：

23  7/7/19 13:51    7/7/19 13:51    0
23  9/3/20 20:23    12/31/99 0:00   6


SELECT ID, ValidFrom, ValidTo, MyValue FROM MyTable FOR SYSTEM_TIME ALL WHERE ID = 23

获取我的值，但我如何获得我想要的两列结果？

以下建议的解决方案不起作用：

WITH data AS
(
    SELECT ID, ValidFrom, MyValue,
    LAG(MyValue, 1) OVER (PARTITION BY ID ORDER BY ValidFrom) prevVal,
    LEAD(MyValue, 1) OVER (PARTITION BY ID ORDER BY ValidFrom) nextVal
    FROM MyTable FOR SYSTEM_TIME ALL WHERE ID = 23
)
SELECT ID, ValidFrom, MyValue
FROM data
WHERE (prevVal IS NOT NULL AND prevVal <> MyValue) OR
(nextVal IS NOT NULL AND nextVal <> MyValue)
ORDER BY ValidFrom DESC;

导致以下结果：

ID  ValidFrom   MyValue
23  2020-09-03 20:23:32.23  6
23  2020-09-03 20:22:00.41  0

标签： sql-servertsql

解决方案

您可以使用 lag() 函数。IE：

with data as
(
 SELECT ID, ValidFrom, ValidTo, MyValue, 
   lag(MyValue,1)  over (/*Partition by id*/ order by validFrom), prevVal
   lead(MyValue,1)  over (/*Partition by id*/ order by validFrom) nextVal
  FROM MyTable WHERE ID = 23
)
select ID, ValidFrom, ValidTo, MyValue
from data
where (prevVal is not null and prevVal <> myValue) OR
(nextVal is not null and nextVal <> myValue);

PS：可能你想通过validFrom订购。

更新：按我的建议由 validFrom 编辑并更正“FOR SYSTEM_TIME”，我不知道它来自哪里。

这是DBFiddle 演示

更新：我对这个问题的理解有所不同（发生变化的地方 - 就像原来的问题一样）。但实际上你所问的只是 MyValue 的第一次出现：

select ID, min(validFrom) ValidFrom, min(validTo) validTo, MyValue
  from x
  where ID = 23
  group by x.ID,MyValue;

这是DBFiddle 演示

推荐阅读