您可以使用递归CTE遍历各行，并将一行与上一行进行比较，并应用归类为过渡的逻辑。
不过，查看您所需的输出，我认为
id = 10它不会出现在列表中，因为它尚未过渡。
您可以单独运行以下示例进行测试：
CREATE TABLE #Data1 ( [id] INT , [data1] INT , [data1high] INT , [data1low] INT );INSERT INTO #Data1 ( [id], [data1], [data1high], [data1low] )VALUES ( 1, 60, 200, 100 ), ( 2, 80, 200, 100 ), ( 3, 123, 200, 100 ), ( 4, 150, 200, 100 ), ( 5, 60, 200, 100 ), ( 6, 60, 200, 100 ), ( 7, 150, 200, 100 ), ( 8, 40, 200, 100 ), ( 9, 58, 200, 100 ), ( 10, 62, 200, 100 ), ( 11, 300, 200, 100 );WITH cte AS ( SELECt TOP 1 id , data1 , data1high , data1low , CASE WHEN data1 < data1low OR data1 > data1high THEN 1 ELSE 0 END AS Transitioned FROM #Data1 ORDER BY id UNIOn ALL SELECt #Data1.id , #Data1.data1 , #Data1.data1high , #Data1.data1low , CASE WHEN cte.data1 < cte.data1low AND #Data1.data1 < #Data1.data1low THEN 0 WHEN cte.data1 > cte.data1high AND #Data1.data1 < #Data1.data1high THEN 0 WHEN cte.data1 BETWEEN cte.data1low AND cte.data1high AND #Data1.data1 BETWEEN #Data1.data1low AND #Data1.data1high THEN 0 WHEN cte.Transitioned = 1 AND #Data1.data1 BETWEEN #Data1.data1low AND #Data1.data1high THEN 1 ELSE 1 END AS Transitioned FROM #Data1 INNER JOIN cte ON cte.id + 1 = #Data1.id ) SELECt * FROM cte WHERe cte.Transitioned = 1DROP TABLE #Data1
在CTE中，将添加一列以标记已转换的行。这些
CASE WHEN子句包含我可以评估的逻辑，如果与上一行相比发生过渡，则需要评估这些逻辑。
在CTE的末尾，您只需选择其中的所有行
Transitioned = 1即可产生：
id data1 data1high data1low Transitioned1 60 200 100 13 123 200 100 15 60 200 100 17 150 200 100 18 40 200 100 111 300 200 100 1
