时间戳

首页 > 解决方案 > 根据条件将一列转换为两列

sql - 根据条件将一列转换为两列

问题描述

我有一张像这样的表：

TagName          DateTime        value
HA_06_ON    2020-07-07 08:52:14    1
HA_06_ON    2020-07-07 09:01:42    0
HA_06_ON    2020-07-07 09:02:17    1
HA_06_ON    2020-07-07 09:32:55    0
HA_06_ON    2020-07-07 09:33:21    1
HA_06_ON    2020-07-07 09:35:02    0
HA_06_ON    2020-07-07 09:35:27    1 
HA_06_ON    2020-07-07 09:35:44    0
HA_06_ON    2020-07-07 10:10:32    1
HA_06_ON    2020-07-07 10:10:40    0

我想将此表转换为此基于值（值 = 1 ==> startTime，值 = 0 ==> EndTime）。

TagName        StartTime                EndTime
HA_06_ON   2020-07-07 08:52:14      2020-07-07 09:01:42
HA_06_ON   2020-07-07 09:02:17      2020-07-07 09:32:55
....

我尝试在 select 语句上使用 case 但在每一列上都返回 null 像这样

TagName           StartTime            EndTime
HA_06_ON      2020-07-07 08:57:07       NULL
HA_06_ON            NULL        2020-07-07 09:01:42
HA_06_ON      2020-07-07 09:02:17       NULL
HA_06_ON            NULL        2020-07-07 09:32:55
HA_06_ON      2020-07-07 09:33:21       NULL
HA_06_ON            NULL        2020-07-07 09:35:02

标签： sqlsql-serversql-server-2014

解决方案

由于您在这里没有分组列，我们必须做出一个假设：我们可以处理您的数据，假设如果我们按日期排序，1 和 0 将交替出现。否则就没有解决方案，因为我们不知道如何将 1 与 0 关联起来。

鉴于我们可以假设这种排序，我们可以使用lag()orlead()来执行此操作。请注意，这假设您从 1 开始，并且每个 1 都有对应的 0。如果最后一个 1 没有对应的 0，则该行的 EndTime 将为空。

select  u.TagName, 
        u.StartTime, 
        u.EndTime 
from    (
        select  TagName, 
                StartTime = [DateTime] ,
                EndTime   = lead([DateTime], 1) over
                            (
                                partition by TagName
                                order by [Datetime] asc
                            ), 
                value 
        from t
        ) u
where   u.value = 1

推荐阅读