假设要求确定是否返回某行取决于从上一个 返回的 行开始经过的时间,则需要采取一种程序方法。递归CTE可能比游标更有效率。
WITH RecursiveCTE
AS (SELECT TOP 1 *
FROM @T
ORDER BY [Timestamp]
UNION ALL
SELECT id,
[Timestamp],
Value
FROM (
--Can't use TOP directly
SELECT T.*,
rn = ROW_NUMBER() OVER (ORDER BY T.[Timestamp])
FROM @T T
JOIN RecursiveCTE R
ON T.[Timestamp] >=
DATEADD(SECOND, 30, R.[Timestamp])) R
WHERE R.rn = 1)
SELECT *
FROM RecursiveCTE