选择“上一行日期”到“当前行日期”

Question

>我有这样的表，

REPORT_ID         USER_ID     ACTION_TYPE ACTION_START            
----------------- ----------- ----------- ----------------------- 
20122511549-621   621         OPEN        2012-02-05 11:05:49.000 
20122511549-621   621         CLOSE       2012-02-19 10:53:28.000 
20132121098-621   621         OPEN        2013-02-12 10:09:08.807 
20132121098-621   621         ACCEPT      2013-02-12 11:16:17.167 
20132121098-621   621         COMMENT     2013-02-12 12:20:17.167 
20132121098-621   621         CLOSE       2013-03-22 16:20:17.167 
201321814390-621  621         OPEN        2013-02-18 14:39:00.157 
201322010148-707  707         OPEN        2013-02-20 10:01:48.693 
201322010148-707  707         RETRIEVE    2013-10-29 13:22:05.017 

我正在尝试的没有多大成功是选择从上一行到当前行的ACTION_START作为prev_action_start

在条件下：

当 REPORT_ID = REPORT_ID 对于除 ACTION_TYPE='OPEN' 以外的所有行时，应ACTION_START当前行作为prev_action_start看起来像这样：

REPORT_ID         USER_ID     ACTION_TYPE ACTION_START            prev_action_start 
----------------- ----------- ----------- ----------------------- ------------------------
20122511549-621   621         OPEN        2012-02-05 11:05:49.000 2012-02-05 11:05:49.000
20122511549-621   621         CLOSE       2012-02-19 10:53:28.000 2012-02-05 11:05:49.000
20132121098-621   621         OPEN        2013-02-12 10:09:08.807 2013-02-12 10:09:08.807
20132121098-621   621         ACCEPT      2013-02-12 11:16:17.167 2013-02-12 10:09:08.807
20132121098-621   621         COMMENT     2013-02-12 12:20:17.167 2013-02-12 11:16:17.167
20132121098-621   621         CLOSE       2013-03-22 16:20:17.167 2013-02-12 12:20:17.167
201321814390-621  621         OPEN        2013-02-18 14:39:00.157 2013-02-18 14:39:00.157
201322010148-707  707         OPEN        2013-02-20 10:01:48.693 2013-02-20 10:01:48.693
201322010148-707  707         RETRIEVE    2013-10-29 13:22:05.017 2013-02-20 10:01:48.693

如果有任何1可以提供帮助，我将不胜感激？

Answer 1

在 SQL Server 2012 中，您可以使用 lag() 函数。 在早期版本中，需要执行显式联接或相关子查询。 我发现后者最容易遵循：

select t.*,
       (case when Action_Type = 'Open' then Action_Start else prev1
        end) as prev_action_start
from (select t.*,
             (select top 1 Action_Start
              from table t2
              where t2.ReportId = t.ReportId and
                    t2.Action_Start < t.Action_Start
              order by Action_Start desc
             ) as prev1
      from table t
     ) t;

您实际上可以将子查询放在case语句中（消除子查询），但我认为将逻辑一分为二会更清楚。