数据之间的时间间隔都超过5分钟时，如何按计数查询和排序数据

不像看起来那么简单，递归 CTE 可以通过 ID 以 1 对 1 的方式合并最接近的记录。

设置：

IF OBJECT_ID('tempdb..#EventData') IS NOT NULL
    DROP TABLE #EventData

CREATE TABLE #EventData (
    RowID INT IDENTITY,
    ID CHAR,
    Event VARCHAR(100),
    Time DATETIME)

INSERT INTO #EventData (
    ID,
    Event,
    Time)
VALUES
    ('A',' EVENT_1','2019-05-07 18:26:39.000'), 
    ('B',' EVENT_1','2019-05-07 18:31:39.000 '), 
    ('C',' EVENT_3','2019-05-07 18:32:39.000'), 
    ('A',' EVENT_2','2019-05-07 18:32:39.000'), 
    ('A',' EVENT_2','2019-05-07 18:33:39.000'), 
    ('A',' EVENT_1','2019-05-07 18:34:39.000'), 
    ('B',' EVENT_2','2019-05-07 18:35:39.000'), 
    ('B',' EVENT_1','2019-05-07 18:36:39.000'), 
    ('C',' EVENT_2','2019-05-07 18:38:39.000'), 
    ('A',' EVENT_1','2019-05-07 18:40:39.000')

解决方案：

;WITH EarliestRecordByID AS
(
    SELECT
        E.ID,
        MinTime = MIN(E.Time)
    FROM
        #EventData AS E
    GROUP BY
        E.ID
),
EventDataWithClosestRecord AS
(
    SELECT
        E.RowID,
        E.ID,
        E.Event,
        E.Time,
        ClosestRowID = T.RowID
    FROM
        #EventData AS E
        OUTER APPLY (
            SELECT TOP 1
                C.RowID
            FROM
                #EventData AS C
            WHERE
                C.ID = E.ID AND
                C.Time > DATEADD(MINUTE, 5, E.Time)
            ORDER BY
                C.Time) AS T
),
RecursiveCTE AS
(
    SELECT
        E.ID,
        E.RowID,
        E.Event,
        E.Time,
        E.ClosestRowID
    FROM
        EventDataWithClosestRecord AS E
        INNER JOIN EarliestRecordByID AS M ON 
            E.ID = M.ID AND
            E.Time = M.MinTime

    UNION ALL

    SELECT
        R.ID,
        D.RowID,
        D.Event,
        D.Time,
        D.ClosestRowID
    FROM
        RecursiveCTE AS R
        INNER JOIN EventDataWithClosestRecord AS D ON R.ClosestRowID = D.RowID
)
SELECT
    R.ID,
    R.RowID,
    R.Event,
    R.Time
FROM
    RecursiveCTE AS R
ORDER BY
    R.Time
OPTION
    (MAXRECURSION 1000) -- Your max recursion level here (0 for unlimited)

结果：

ID  RowID   Event       Time
A   1       EVENT_1     2019-05-07 18:26:39.000
B   2       EVENT_1     2019-05-07 18:31:39.000
C   3       EVENT_3     2019-05-07 18:32:39.000
A   4       EVENT_2     2019-05-07 18:32:39.000
C   9       EVENT_2     2019-05-07 18:38:39.000
A   10      EVENT_1     2019-05-07 18:40:39.000

可能还有另一种（可能更快）使用窗口函数的解决方案，因为与非递归解决方案相比，SQL Server 中的递归解决方案的性能非常差。