我如何在给定之前和之后获得记录? [英] How do I get records before and after given one?

问题描述

我有以下表结构:

Id, Message
1, John Doe
2, Jane Smith
3, Error
4, Jane Smith

有没有办法获取错误记录和周围的记录?即查找所有错误及其前后的记录.

Is there a way to get the error record and the surrounding records? i.e. find all Errors and the record before and after them.

推荐答案

;WITH numberedlogtable AS
(
SELECT Id,Message, 
ROW_NUMBER() OVER (ORDER BY ID) AS RN
 FROM logtable
)

SELECT Id,Message
FROM numberedlogtable
WHERE RN IN (SELECT RN+i
             FROM numberedlogtable
             CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
             WHERE Message='Error')

这篇关于我如何在给定之前和之后获得记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！