时间戳

首页 > 解决方案 > 匹配以前和当前的记录有和没有滞后和领先

sql - 匹配以前和当前的记录有和没有滞后和领先

问题描述

我有如下表。记录没有任何主键，我想在 WITH 和 WITHOUT LAG 和 LEAD 功能中实现它。

ID      ENTID     INOUTDATE             YEAR    MONTH   STATUS
1923    1923    [NULL]                  2099     12      Out
1923    10690   [NULL]                  2099     12      Out
1923    9670    2012-08-24 00:00:00     2012     8       In
1923    1923    2013-06-01 00:00:00     2013     6       In
1923    9670    2018-04-19 00:00:00     2018     4       Out
1923    10690   2019-02-01 00:00:00     2019     2       In

我想按以下方式获取记录。

ID      ENTID     INOUTDATE             YEAR    MONTH   STATUS
1923    10690   [NULL]                  2099    12      Out
1923    9670    2012-08-24 00:00:00     2012    8       In
1923    9670    2018-04-19 00:00:00     2018    4       Out
1923    10690   2019-02-01 00:00:00     2019    2       In

标签： sqlsql-server

解决方案

lag()是最简单的方法：

select t.*
from (select t.*, 
             lag(status) over (partition by id, (case when inoutdate is null then 1 else 2 end)
                               order by inoutdate
                              ) as prev_status
      from t
     ) t
where prev_status is null or prev_status <> status;

您可以将此视为组和岛屿问题，使用 row_number(). 逻辑更复杂：

select t.*
from (select t.*, 
             row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end), status, (seqnum - seqnum_s)
                                order by inoutdate
                               ) as seqnum_g
      from (select t.*,
                   row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end) order by inoutdate) as seqnum,
                   row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end), status order by inoutdate) as seqnum_s
            from t
           ) t
     ) t
where seqnum_g = 1;

推荐阅读