查找状态字段与当前字段不同的下一条记录 [英] Find next record where status field is different from current

问题描述

我有一个用于记录事件的表。

I have a table that is used to log events. Two types specifically : ON and OFF.

有时会有重叠的日志条目，因为可以同时记录2个设备。这不是至关重要的，因为最终报告应大致（正确）概述ON-> OFF期间。

There are sometimes overlapping log entries as there can be 2 simultaneous devices logging. This is not crucial, as the end report should give a [mostly] correct overview of ON -> OFF periods.

下面是一个示例，第3列仅用于图示： 它不存在。

Below is a sample, with the 3rd column just for illustration: It does not exist.

ActionTaken    ID   ID_of_next_OFF
Switched ON    1    3
Switched ON    2    6
Switched OFF   3    
Switched ON    4    7
Switched ON    5    8
Switched OFF   6    
Switched OFF   7    
Switched OFF   8    
Switched On    9    10
Switched OFF   10   
Switched On    11   12
Switched OFF   12   

鉴于前两列，如何计算第三列？

Given the first two columns, how can I calculate the third?

这不起作用：

SELECT actionTaken, Id, LEAD(Id) 
OVER (PARTITION BY ActionTaken ORDER BY ID) nextConn 
FROM dbo.Events

因为ID_of_Next基于下一个匹配的actionTaken值，而不是nex t交替。

as it bases the ID_of_Next on the next matching actionTaken value, instead of the next alternate.

推荐答案

您的方法正确。您需要做的是已打开 部分中的左联接和已切换部分

You are on the right way. All you need is the LEFT JOIN of the 'Switched ON' part with the 'Switched OFF' part on equal row numbers.

with Events as (
  select 'Switched ON' as ActionTaken, 1 as ID union all -- 3
  select 'Switched ON', 2 union all -- 6
  select 'Switched OFF', 3 union all
  select 'Switched ON', 4 union all -- 7
  select 'Switched ON', 5 union all -- 8
  select 'Switched OFF', 6 union all
  select 'Switched OFF', 7 union all
  select 'Switched OFF', 8 union all
  select 'Switched On', 9 union all -- 10
  select 'Switched OFF', 10 union all
  select 'Switched On', 11 union all -- 12
  select 'Switched OFF', 12
), E as (
  select
    *, row_number() over(partition by ActionTaken order by ID) as rn
  from Events
)
select
  a.ActionTaken, a.ID, b.ID
from E as a
left join E as b
  on a.ActionTaken = 'Switched ON' and
     b.ActionTaken = 'Switched OFF' and
     a.rn = b.rn
order by a.ID, a.ActionTaken;

输出：

+--------------+----+------+
| ActionTaken  | ID |  ID  |
+--------------+----+------+
| Switched ON  |  1 | 3    |
| Switched ON  |  2 | 6    |
| Switched OFF |  3 | NULL |
| Switched ON  |  4 | 7    |
| Switched ON  |  5 | 8    |
| Switched OFF |  6 | NULL |
| Switched OFF |  7 | NULL |
| Switched OFF |  8 | NULL |
| Switched On  |  9 | 10   |
| Switched OFF | 10 | NULL |
| Switched On  | 11 | 12   |
| Switched OFF | 12 | NULL |
+--------------+----+------+

使用 SQL小提琴在线进行测试。

这篇关于查找状态字段与当前字段不同的下一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！