如何计算表中连续行之间的时间差？

How to Calculate Time Difference in Two Consecutive Table Rows

In a table with a column called "StartDate," it's often necessary to determine the time difference between adjacent records. One user recently encountered this issue while working with a requestId and startdate table and sought assistance.

The table provided by the user contained data as follows:

requestId	startdate
1	2011-10-16 13:15:56
2	2011-10-16 13:15:59
3	2011-10-16 13:15:59
4	2011-10-16 13:16:02
5	2011-10-16 13:18:07

The user's objective was to calculate the time difference between each adjacent pair of rows (e.g., requestId 1 and 2, 2 and 3, etc.). They recognized the need for a self-join but struggled to write the appropriate ON clause.

The provided solution leveraged an INNER JOIN between two instances of the same table (aliased as A and B). The ON clause specified that the requestId in row B should equal the requestId in row A plus one:

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A INNER JOIN MyTable B ON B.requestid = (A.requestid + 1)
ORDER BY A.requestid ASC

This query successfully calculated and returned the requested time difference between consecutive rows. However, if the requestId is not consecutive, an alternative approach is required:

SELECT A.requestid, A.starttime, (B.starttime - A.starttime) AS timedifference
FROM MyTable A CROSS JOIN MyTable B
WHERE B.requestid IN (SELECT MIN (C.requestid) FROM MyTable C WHERE C.requestid > A.requestid)
ORDER BY A.requestid ASC

In this modified query, a CROSS JOIN is used to match each row with every other row in the table. A subquery then selects the minimum requestId that is greater than the current selected requestId, ensuring that only consecutive rows are compared.

以上是如何计算表中连续行之间的时间差？的详细内容。更多信息请关注PHP中文网其他相关文章！