您可以尝试以下操作：

WITH CTE AS
(
  SELECT D.[DateTime], D.Message, SUM(D.F) OVER (ORDER BY [DateTime]) FLAG
  FROM
  (
    SELECT *,CASE WHEN Message ='Test2' OR Message='Test4' THEN 1 ELSE 0 END AS F
    FROM table_name
  ) D
)
SELECT [DateTime], Message FROM CTE T 
WHERE(
        NOT EXISTS(SELECT 1 FROM CTE D WHERE D.MESSAGE='Test2' AND D.FLAG=T.FLAG)
        AND NOT EXISTS(SELECT 1 FROM CTE D WHERE D.MESSAGE='Test4' AND D.FLAG=T.FLAG+1)
        AND T.MESSAGE='Test1'
     ) 
     OR T.MESSAGE <> 'Test1'
     OR T.FLAG = 0

此查询将过滤掉‘Test2’和‘Test4’之间的所有‘Test1’值，但不会过滤掉‘Test4’和‘Test2’之间的所有值，这是根据您的要求而定的；“‘Test2’是我的起点，‘Test4’是我的终点”。

请参见demo。

我尝试了这个查询，我可以执行它：，

WITH cte AS (
    SELECT
        datetime,
        message
    FROM
        mytable
    WHERE
        datetime >= ( SELECT TOP 1 datetime d1 FROM mytable WHERE message = 'Test2' ) 
        AND
        datetime <= ( SELECT TOP 1 datetime d2 FROM mytable WHERE message = 'Test4' )
        AND
        message = 'Test1'
)
SELECT
    mt.datetime,
    mt.message
FROM
    mytable mt
    LEFT OUTER JOIN cte ON
        mt.datetime = cte.datetime
        AND
        mt.message = cte.message
WHERE
    cte.datetime IS NULL
    AND
    cte.message IS NULL

(我是第一次接触堆栈溢出；如果代码中有任何错误或不是正确的方式，我希望收到反馈)。

select  DateTime
       ,last_group as Message
from    (
        select *
               ,case when Message = 'Test1' and start_end = 1 then null else Message end as last_group 
        from   (
                select * 
                       ,count(case when Message = 'Test2' then 1 when Message = 'Test4' then 1 end) over(order by DateTime) as start_end
                from t
               ) t
         ) t
where    last_group is not null

DateTime|消息

2022-09-09 00：00：00.000|测试1
2022-09-09 01：00：00.000|测试2
2022-09-09 04：00：00.000|测试3
2022-09-09 05：00：00.000|测试4
2022-09-09 06：00：00.000|测试1

Fiddle