如何在SQL查询中仅显示每次迭代的首尾Idle状态

我在编写SQL查询时需要实现以下逻辑：

• 仅保留每次连续Idle状态迭代过程中的第一条和最后一条记录
• Active与Stop状态的显示逻辑保持不变，无需调整

以当前场景为例，仅需呈现tagid为4886和4889的Idle记录，中间的Idle条目（4887、4888）不需要显示。

当前查询语句 #

WITH LaggedHyd101 AS (
    SELECT
        [tagid],
        [recordtime],
        [D1],
        [D2],
        [D3],
        [D4],
        [D5],
        [D6],
        [D7],
        [D8],
        [D9],
        [D10],
        [D11],
        [D12],
        [D13],
        [D14],
        [D15],
        [D16],
        [D17],
        [D18],
        LEAD([D17]) OVER (ORDER BY [recordtime] DESC) AS LaggedD17,
        LEAD([D18]) OVER (ORDER BY [recordtime] DESC) AS LaggedD18
    FROM
        [INFOU_CCR23].[dbo].[HYD101]
)
SELECT
    [tagid],
    [recordtime] AS StartTime,
    LAG([recordtime]) OVER (ORDER BY [recordtime] DESC) AS StopTime,
    [D1],
    [D2],
    [D3],
    [D4],
    [D5],
    [D6],
    [D7],
    [D8],
    [D9],
    [D10],
    [D11],
    [D12],
    [D13],
    [D14],
    [D15],
    [D16],
    [D17],
    [D18],
    ABS(DATEDIFF(MINUTE, LAG([recordtime]) OVER (ORDER BY [recordtime] DESC), [recordtime])) AS DifferentTime,
    CASE
        WHEN ([D18] = 0 AND [D17] = 0) THEN 'Stop'
        WHEN ([D18] = 1 AND [D17] > 0 ) THEN 'Active'
        WHEN ([D18] = 1 AND  LaggedD17 = 0) THEN 'Idle'
        ELSE NULL
    END AS Status
FROM
    LaggedHyd101
WHERE
    ([D18] = 0 AND (LaggedD18 IS NULL OR LaggedD18 = 1))
    OR
    ([D18] = 1 AND (LaggedD18 IS NULL OR LaggedD18 = 0))
    OR
    ([D18] = 1  AND (LaggedD18 IS NULL OR LaggedD17 = 0 ))
ORDER BY
    [recordtime] DESC;

期望的正确查询结果 #

tagid   StartTime           StopTime            D1      D2  D3  D4  D5  D6  D7  D8  D9  D10 D11 D12 D13 D14 D15 D16 D17 D18 DifferentTime   Status
5851    2024-01-09 14:56:08.063 NULL                HYD-101     375 137 50  69  1500                                                        1500    900 4054                            650 1   NULL    Active
5850    2024-01-09 14:55:10.470 2024-01-09 14:56:08.063 HYD-101     381 47  50  11  1501                                                        1500    0   4054                            0   1   1   Idle
5565    2024-01-09 10:10:10.467 2024-01-09 14:55:10.470 HYD-101     387 0   0   0   0                                                       1500    0   4054                            0   0   285 Stop
5475    2024-01-09 08:40:10.470 2024-01-09 10:10:10.467 HYD-101     376 123 50  62  1500                                                        1500    900 4053                            650 1   90  Active
5105    2024-01-09 02:30:10.467 2024-01-09 08:40:10.470 HYD-101     383 0   0   0   0                                                       1500    0   4053                            0   0   370 Stop
5019    2024-01-09 01:04:08.063 2024-01-09 02:30:10.467 HYD-101     382 45  50  9   1501                                                        1500    0   4051                            400 1   86  Active
5018    2024-01-09 01:03:08.060 2024-01-09 01:04:08.063 HYD-101     381 45  50  9   1501                                                        1500    0   4051                            0   1   1   Idle
5002    2024-01-09 00:47:08.067 2024-01-09 01:03:08.060 HYD-101     384 0   0   0   0                                                       1500    0   4051                            0   0   16  Stop
4890    2024-01-08 22:55:10.470 2024-01-09 00:47:08.067 HYD-101     374 124 50  62  1501                                                        1500    900 4049                            680 1   112 Active
4889    2024-01-08 22:54:08.063 2024-01-08 22:55:10.470 HYD-101     377 44  50  9   1500                                                        1500    0   4049                            0   1   1   Idle
4886    2024-01-08 22:51:08.063 2024-01-08 22:52:08.070 HYD-101     378 44  50  9   1501                                                        1500    0   4049                            0   1   1   Idle
4801    2024-01-08 21:26:08.063 2024-01-08 22:51:08.063 HYD-101     375 121 50  60  1501                                                        1500    900 4048                            680 1   85  Active
4678    2024-01-08 19:23:08.060 2024-01-08 21:26:08.063 HYD-101     425 45  7   -1  304                                                     1500    0   4048                            0   0   123 Stop
4677    2024-01-08 19:22:08.063 2024-01-08 19:23:08.060 HYD-101     380 52  50  17  1500                                                        1500    0   4048                            0   1   1   Idle
4608    2024-01-08 18:13:08.063 2024-01-08 19:22:08.063 HYD-101     383 0   0   0   0                                                       1500    0   4048                            0   0   69  Stop
4486    2024-01-08 16:11:08.063 2024-01-08 18:13:08.063 HYD-101     372 123 50  61  1500                                                        1500    900 4046                            670 1   122 Active

内容的提问来源于stack exchange，提问作者hendry prakoso