SQL Server：如何查询警告日志时间戳前后5分钟的所有日志行？

问题描述 #

我有一个日志表Logs，包含id、TimeStamp、Message字段，其中Message字段的值有时包含'WARNING'。

SELECT TimeStamp 
FROM Logs 
WHERE Message LIKE '%WARNING%'

需要获取所有时间戳在上述查询结果中每个时间点前后5分钟范围内的日志行。已知单个时间值可以用BETWEEN结合DATEADD实现，但不知道如何处理一组时间值？是否可以结合CTE和UNION ALL实现，还是必须遍历结果集？

更新尝试
尝试了以下CTE写法，但结果只包含带有WARNING的记录，时间范围内的周边记录都没有出现：

declare @searchTerm varchar(50) = '%WARNING%';
declare @searchDate datetime = '2023-11-01';
declare @id int;
declare @eventTime datetime;
declare @lowRange int = -5;
declare @highRange int = 5;

WITH cteEvents as (
    select Id, EventTime from Veneer.dbo.Logs where EventMessage LIKE @searchTerm and EventTime > @searchDate
)
SELECT * 
    from Logs l
    left outer join cteEvents ev on l.Id=ev.Id 
    where l.EventTime between DATEADD(MINUTE, @lowRange, ev.EventTime) and DATEADD(MINUTE, @highRange, ev.EventTime)

错误原因 #

你的JOIN条件用了l.Id=ev.Id，这只能匹配到警告日志本身——只有警告日志的ID和CTE中记录的ID一致，周边日志的ID和CTE里的ID不匹配，自然筛选不出来。正确的关联逻辑应该是判断日志时间是否落在任意一条警告日志的前后5分钟范围内，而非关联ID。

解决方案 #

方案1：使用EXISTS子查询 #

这种写法逻辑清晰，直接判断当前日志是否存在对应的警告时间范围覆盖它：

DECLARE @searchTerm VARCHAR(50) = '%WARNING%';
DECLARE @searchDate DATETIME = '2023-11-01';
DECLARE @rangeMinutes INT = 5;

SELECT *
FROM Logs l
WHERE l.EventTime > @searchDate
AND EXISTS (
    SELECT 1
    FROM Logs w
    WHERE w.Message LIKE @searchTerm
    AND w.EventTime > @searchDate
    AND l.EventTime BETWEEN DATEADD(MINUTE, -@rangeMinutes, w.EventTime) 
                        AND DATEADD(MINUTE, @rangeMinutes, w.EventTime)
);

方案2：使用CTE+时间范围JOIN #

先提取所有警告时间，再通过时间范围关联所有符合条件的日志，注意用DISTINCT避免同一日志被多个警告范围重复匹配：

DECLARE @searchTerm VARCHAR(50) = '%WARNING%';
DECLARE @searchDate DATETIME = '2023-11-01';
DECLARE @lowRange INT = -5;
DECLARE @highRange INT = 5;

WITH cteWarnings AS (
    SELECT EventTime AS WarningTime
    FROM Logs
    WHERE Message LIKE @searchTerm
    AND EventTime > @searchDate
)
SELECT DISTINCT l.*
FROM Logs l
JOIN cteWarnings w ON 
    l.EventTime BETWEEN DATEADD(MINUTE, @lowRange, w.WarningTime) 
                    AND DATEADD(MINUTE, @highRange, w.WarningTime)
WHERE l.EventTime > @searchDate;

内容的提问来源于stack exchange，提问作者Hey Mikey