SQL Server DATEDIFF 行间计算列 [英] SQL Server DATEDIFF Computed Column Between Rows

问题描述

我目前有一个类似的表 -

I currently have a table similar to this -

RecordTime                    Running     Fault
-----------------------------------------------
2016-09-15 11:32:01.00        0           202
2016-09-15 11:32:08.00        1           202
2016-09-15 11:39:31.00        0           21
2016-09-15 11:40:07.00        1           4
2016-09-15 11:42:11.00        0           21
2016-09-15 11:42:39.00        1           45

然后我想计算每条记录的 RecordTimes 之间的时间差.为此，我使用以下 -

I then wanted to calculate the time difference between the RecordTimes for each record. For this I am using the following -

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY RecordTime) AS rn
        FROM    dbo.Table1
        )
SELECT  DATEDIFF(second, mc.RecordTime, mp.RecordTime)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

哪个返回 -

(No column name)
----------------
7
443
36
124
28
303

不过，我真正想做的是在原始表中创建一个计算列，为我提供这些值.这可能吗?

What I actually want to do, though, is create a computed column in the original table that gives me these values. Is this possible?

我认为我可以将查询转换为 UDF，然后在列中引用它，但我对这种工作的经验不是很丰富.

I thought that I might be able to convert the query into a UDF and then reference that in the column, but I'm not very experienced with that kind of work.

---编辑---

最终结果应该是 -

RecordTime                    Running     Fault     Diff
--------------------------------------------------------
2016-09-15 11:32:01.00        0           202       7
2016-09-15 11:32:08.00        1           202       443
2016-09-15 11:39:31.00        0           21        36
2016-09-15 11:40:07.00        1           4         124
2016-09-15 11:42:11.00        0           21        28
2016-09-15 11:42:39.00        1           45        303

推荐答案

我建议您为此目的使用视图:

I recommend you to use a view for this purpose:

CREATE VIEW Table1_vw 
AS
WITH cte AS (
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY RecordTime) AS rn
    FROM    dbo.Table1
)
SELECT  mc.RecordTime,
        mc.Running,
        mc.Fault,
        DATEDIFF(second, mc.RecordTime, mp.RecordTime) Diff
FROM cte mc
LEFT JOIN cte mp
ON mc.rn = mp.rn - 1

由于您使用的是 SQL Server 2012，您可以使用 LEAD 功能:

Since you are using SQL Server 2012 you can use LEAD function:

CREATE VIEW Table1_vw 
AS
SELECT  RecordTime,
        Running,
        Fault,
        DATEDIFF(second,RecordTime,LEAD(RecordTime,1,NULL) OVER (ORDER BY RecordTime ASC) ) as Diff
FROM Table1
GO

这篇关于SQL Server DATEDIFF 行间计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！