温馨提示:本文翻译自stackoverflow.com,查看原文请点击:sql server - Find next record where status field is different from current

sql server - 查找状态字段与当前字段不同的下一条记录

发布于 2020-03-27 11:44:26

我有一个用于记录事件的表。具体有两种类型:ON和OFF。

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

下面是一个示例,第3列仅用于说明:不存在。

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   

给定前两列,如何计算第三列?

这不起作用:

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

因为ID_of_Next基于下一个匹配的actionTaken值,而不是下一个替代值。

查看更多

查看更多

提问者
callisto
被浏览
48
Andrei Odegov 2019-07-03 23:24

您的方法正确。所有你需要的是LEFT JOIN对的'Switched ON'同一部分'Switched OFF'上等于行号的一部分。

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 Fiddle在线进行测试

发布
问题

分享
好友

手机
浏览

扫码手机浏览