时间戳

首页 > 解决方案 > 如何使用 SQL 查询选择 InTime 和 OutTime

sql - 如何使用 SQL 查询选择 InTime 和 OutTime

问题描述

我有一张名为tblEventRegister(Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50)).

该表有以下数据：

EventDate                     TagName  EventName    TagValue       
2021-03-18 12:06:35.547        A1        E1           1    
2021-03-18 12:06:37.547        A2        E1           1       
2021-03-18 12:06:38.547        A3        E1           1         
2021-03-18 12:06:45.547        A1        E1           0     
2021-03-18 12:06:47.547        A1        E1           0 
2021-03-18 12:06:48.547        A1        E1           1 
2021-03-18 12:06:55.547        A1        E1           0 
2021-03-18 12:06:56.547        A1        E1           0 
2021-03-18 12:06:57.547        A1        E1           1

当 TagValue=1 时 EventDate 为 InTime，当 tagvalue 为 0 时 EventDate 为 OutTime。我使用以下查询来查找 InTime 和 OutTime。

select EventDate, TagName, EventName, Tagvalue
    , (case Tagvalue when 1 then EventDate end) intime
    , (case Tagvalue when 0 then EventDate end) outtime
from tblEventRegister with (nolock)

来的结果是

 EventDate               TagName  EventName TagValue       InTime              OutTime  
2021-03-18 12:06:35.547   A1        E1        1    2021-03-18 12:06:35.547      Null
2021-03-18 12:06:37.547   A2        E1        1    2021-03-18 12:06:37.547      Null
2021-03-18 12:06:38.547   A3        E1        1    2021-03-18 12:06:38.547      Null
2021-03-18 12:06:45.547   A1        E1        0            Null               2021-03-18 12:06:45.547
2021-03-18 12:06:47.547   A2        E1        0            Null               2021-03-18 12:06:47.547
2021-03-18 12:06:48.547   A1        E1        1    2021-03-18 12:06:48.547      Null
2021-03-18 12:06:55.547   A3        E1        0            Null               2021-03-18 12:06:55.547
2021-03-18 12:06:56.547   A1        E1        0            Null               2021-03-18 12:06:56.547
2021-03-18 12:06:57.547   A1        E1        1    2021-03-18 12:06:56.547      Null

但结果应该是

EventDate               TagName  EventName TagValue       InTime               OutTime  
2021-03-18 12:06:35.547   A1        E1        1    2021-03-18 12:06:35.547    2021-03-18 12:06:45.547
2021-03-18 12:06:37.547   A2        E1        1    2021-03-18 12:06:37.547    2021-03-18 12:06:47.547
2021-03-18 12:06:38.547   A3        E1        1    2021-03-18 12:06:38.547    2021-03-18 12:06:55.547   
2021-03-18 12:06:48.547   A1        E1        1    2021-03-18 12:06:48.547    2021-03-18 12:06:56.547
2021-03-18 12:06:57.547   A1        E1        1    2021-03-18 12:06:57.547      Null

我怎样才能做到这一点？

标签： sqlsql-servertsqlsql-server-2014

解决方案

以下返回您想要的结果......但请注意，它没有考虑任何其他可能的数据问题。所以它假设每个时间都会有一个匹配的时间，或者没有时间。但它不会处理多次进出时间。

它的工作原理是为每个 TagName、TagValue 对分配一个行号，然后将它们连接起来。

declare @tblEventRegister table (Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50));

insert into @tblEventRegister (EventDate, TagName, EventName, TagValue)
values
('2021-03-18 12:06:35.547', 'A1', 'E1', 1),
('2021-03-18 12:06:37.547', 'A2', 'E1', 1),
('2021-03-18 12:06:38.547', 'A3', 'E1', 1),
('2021-03-18 12:06:45.547', 'A1', 'E1', 0),
('2021-03-18 12:06:47.547', 'A2', 'E1', 0),
('2021-03-18 12:06:48.547', 'A1', 'E1', 1),
('2021-03-18 12:06:55.547', 'A3', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 1);

with cte as (
    select *
        , row_number() over (partition by TagName, TagValue order by EventDate) rn
    from @tblEventRegister
)
select C1.EventDate, C1.TagName, C1.EventName, C1.TagValue, C1.Eventdate, C2.EventDate
from cte C1
left join cte C2 on C2.TagName = C1.TagName and C2.TagValue = 0 and C1.rn = C2.rn
where C1.TagValue = 1
order by C1.EventDate asc;

回报：

活动日期 标签名 事件名称 标签值 活动日期 活动日期
2021-03-18 12:06:35.547 A1 E1 1 2021-03-18 12:06:35.547 2021-03-18 12:06:45.547
2021-03-18 12:06:37.547 A2 E1 1 2021-03-18 12:06:37.547 2021-03-18 12:06:47.547
2021-03-18 12:06:38.547 A3 E1 1 2021-03-18 12:06:38.547 2021-03-18 12:06:55.547
2021-03-18 12:06:48.547 A1 E1 1 2021-03-18 12:06:48.547 2021-03-18 12:06:56.547
2021-03-18 12:06:56.547 A1 E1 1 2021-03-18 12:06:56.547 无效的

注意 1：这显示了如何将您的示例数据显示为 DDL+DML - 我鼓励您在未来将其包含在内以加快响应速度。

注意 2：不要with (nolock)用作某种默认查询设置，因为结果可能出乎意料。只有在完全理解后果并且绝对必须这样做的情况下，才应该使用该提示。

推荐阅读