SQL Server 2008 中超前滞后函数的替代方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22188514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alternate of lead lag function in SQL Server 2008
提问by Mahajan344
I want to compare the current row with a value in the next row. SQL has LEADand LAGfunctions to get the next and previous values but I can not use them because I am using SQL Server 2008.
我想将当前行与下一行中的值进行比较。SQL具有LEAD和LAG函数来得到下一个和前值，但我不能使用它们，因为我使用SQL Server 2008。
So how do I get this?
那我怎么得到这个？
I have table with output
我有输出表
+----+-------+-----------+-------------------------+
| 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 |
+----+-------+-----------+-------------------------+
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.
我想要做的是，如果当前行状态为 1，下一行状态为 6 并且两个时间相同（最多几分钟），那么我想获取状态为 1 的行。
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
例如：Id 6 行的状态为 1，Id 7 行的状态为 6，但两者时间相同，即。2014-02-19 06:11
So I want to get this row or id for status 1 ie. id 6
所以我想获得状态 1 的这一行或 ID，即。编号 6
回答by Gordon Linoff
In your case, the ids appear to be numeric, you can just do a self-join:
在您的情况下，ids 似乎是数字，您可以进行自联接：
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;
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:
这不是完全相同的分钟。它在 60 秒内。你真的需要相同的日历时间分钟吗？如果是这样，你可以这样做：
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);
回答by ScottLenart
Just posting a more complex join using two different tables created with Gordon's foundation. Excuse the specific object names, but you'll get the gist. Gets the percentage change in samples from one to the next.
只需使用 Gordon 基金会创建的两个不同表发布更复杂的连接。请原谅特定的对象名称，但您会明白要点。获取样本从一个到下一个的百分比变化。
SELECT
fm0.SAMPLE curFMSample
, fm1.SAMPLE nextFMSample
, fm0.TEMPERATURE curFMTemp
, fm1.TEMPERATURE nextFMTemp
, ABS(CAST((fm0.Temperature - fm1.Temperature) AS DECIMAL(4, 0)) / CAST(fm0.TEMPERATURE AS DECIMAL(4, 0))) AS fmTempChange
, fm0.GAUGE curFMGauge
, fm1.GAUGE nextFMGauge
, ABS(CAST((fm0.GAUGE - fm1.GAUGE) AS DECIMAL(4, 4)) / CAST(fm0.GAUGE AS DECIMAL(4, 4))) AS fmGaugeChange
, fm0.WIDTH curFMWidth
, fm1.WIDTH nextFMWidth
, ABS(CAST((fm0.Width - fm1.Width) AS DECIMAL(4, 2)) / CAST(fm0.Width AS DECIMAL(4, 2))) AS fmWidthChange
, cl0.TEMPERATURE curClrTemp
, cl1.TEMPERATURE nextClrTemp
, ABS(CAST((cl0.Temperature - cl1.Temperature) AS DECIMAL(4, 0)) / CAST(cl0.TEMPERATURE AS DECIMAL(4, 0))) AS clrTempChange
FROM
dbo.COIL_FINISHING_MILL_EXIT_STR02 fm0
INNER JOIN dbo.COIL_FINISHING_MILL_EXIT_STR02 fm1 ON (fm0.SAMPLE = fm1.SAMPLE - 1 AND fm1.coil = fm0.coil)
INNER JOIN dbo.COIL_COILER_STR02 cl0 ON fm0.coil = cl0.coil AND fm0.SAMPLE = cl0.SAMPLE
INNER JOIN dbo.COIL_COILER_STR02 cl1 ON (cl0.SAMPLE = cl1.SAMPLE - 1 AND cl1.coil = cl0.coil)
WHERE
fm0.coil = 2015515872
回答by Art
Well, I would suggest a very simple solution if you do not have a sequential row id but a different step (if some records were deleted for example..):
好吧，如果您没有连续的行 id 但有不同的步骤（例如，如果删除了某些记录..），我会建议一个非常简单的解决方案：
declare @t table(id int, obj_name varchar(5))
insert @t select 1,'a'
insert @t select 5,'b'
insert @t select 22,'c'
insert @t select 543,'d'
---------------------------------
select *from @t
Example Source Table @t:
示例源表@t：
---------------------------------
id obj_name
1 a
5 b
22 c
543 d
---------------------------------
Select with self join
选择自加入
select obj_name_prev=tt.obj_name, obj_name_next=min(t.obj_name)
from @t t
join @t tt on tt.id < t.id
group by tt.obj_name
Result:
结果：
---------------------------------
obj_name_prev obj_name_next
a b
b c
c d
---------------------------------
