选择sql中时差最小的10行

如果您使用的是SQL Server 2005或更新版本,请尝试此查询:

WITH T1 AS (
    SELECT
        userID,
        registrationDate,
        ROW_NUMBER() OVER (ORDER BY registrationDate) AS rn
    FROM Users
), T3 AS (
    SELECT
        T1.registrationDate AS interval_start,
        T2.registrationDate AS interval_end,
        T1.registrationDate - T2.registrationDate AS diff
    FROM T1
    JOIN T1 T2
   ON T1.rn = T2.rn + 5
)
SELECT TOP 1 interval_start, interval_end
FROM T3
ORDER BY diff