SQL Server 2008 中超前滞后函数的替代方法 [英] Alternate of lead lag function in SQL Server 2008

问题描述

我想将当前行与下一行中的值进行比较.SQL 具有 LEAD 和 LAG 函数来获取下一个和上一个值，但我无法使用它们，因为我使用的是 SQL Server 2008.

I want to compare the current row with a value in the next row. SQL has LEAD and LAG functions to get the next and previous values but I can not use them because I am using SQL Server 2008.

那我怎么得到这个?

我有输出表

+----+-------+-----------+-------------------------+
| Id | ActId |  StatusId |       MinStartTime      |
+----+-------+-----------+-------------------------+
| 1  |  42   | 1         | 2014-02-14 11:17:21.203 |
| 2  |  42   | 1         | 2014-02-14 11:50:19.367 |
| 3  |  42   | 1         | 2014-02-14 11:50:19.380 |
| 4  |  42   | 6         | 2014-02-17 05:25:57.280 |
| 5  |  42   | 6         | 2014-02-19 06:09:33.150 |
| 6  |  42   | 1         | 2014-02-19 06:11:24.393 |
| 7  |  42   | 6         | 2014-02-19 06:11:24.410 |
| 8  |  42   | 8         | 2014-02-19 06:44:47.070 |
+----+-------+-----------+-------------------------+

我想要做的是，如果当前行状态为 1，下一行状态为 6 并且两个时间相同(最多几分钟)，那么我想获取状态为 1 的行.

What I want to do is if the current row status is 1 and the next row status is 6 and both times are the same (up to minutes) then I want to get the row where the status is 1.

例如:Id 6 行的状态为 1，Id 7 行的状态为 6，但两者时间相同，即.2014-02-19 06:11

Eg: Id 6 row has status 1 and Id 7 row has status 6 but both times are the same ie. 2014-02-19 06:11

所以我想获取状态 1 的这一行或 ID，即.编号 6

So I want to get this row or id for status 1 ie. id 6

推荐答案

在您的情况下，id 似乎是数字，您可以进行自联接:

In your case, the ids appear to be numeric, you can just do a self-join:

select t.*
from table t join
     table tnext
     on t.id = tnext.id - 1 and
        t.StatusId = 1 and
        tnext.StatusId = 6 and
        datediff(second, t.MinStartTime, tnext.MinStartTime) < 60;

这不是同一分钟.它在 60 秒内.你真的需要相同的日历时间分钟吗?如果是这样，你可以这样做:

This isn't quite the same minute. It is within 60 seconds. Do you actually need the same calendar time minute? If so, you can do:

select t.*
from table t join
     table tnext
     on t.id = tnext.id - 1 and
        t.StatusId = 1 and
        tnext.StatusId = 6 and
        datediff(second, t.MinStartTime, tnext.MinStartTime) < 60 and
        datepart(minute, t.MinStartTime) = datepart(minute, tnext.MinStartTime);

这篇关于SQL Server 2008 中超前滞后函数的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！