TSQL 查询返回相距 5 分钟之内的所有行

2021-09-10 00:00:00 tsql sql-server

我想返回表中相距在 5 分钟内的所有行.

I want to return all the rows in a table that are within 5 mins of each other.

示例表:

KillTime 是我们要查询的 5 分钟内

我已经尝试在 JOINs 和 DATEADD 时间之前做到这一点，但我似乎无法做到这一点.

I have tried to do this by JOINs and DATEADD time but i do not seem to be able to quite get there.

推荐答案

DATEADD 是一个选项，但它变得有点复杂.更好的选择是使用 DATEDIFF:

DATEADD is an option, but it gets a little complicated. A better option is to use DATEDIFF:

--Test Setup:
DECLARE @sourceTable AS TABLE (KillID int PRIMARY KEY IDENTITY(1,1), KillTime datetime2(7) NOT NULL);
INSERT INTO @sourceTable (KillTime)
VALUES
    ('2016/02/02 10:01'),
    ('2016/02/02 10:05'),
    ('2016/02/02 10:09'),
    ('2016/02/02 10:30')

--Code:
SELECT *
FROM        @sourceTable AS ST1
INNER JOIN  @sourceTable AS ST2
    ON      ST2.KillID < ST1.KillID                     --Only the smaller IDs so we do not get self joins or duplicates (1 to 2 and 2 to 1).
        AND DATEDIFF(second, ST2.KillTime, ST1.KillTime) BETWEEN -300 AND 300;  --300 seconds is 5 minutes

使用秒而不是分钟来减少舍入/截断问题.

Use seconds instead of minutes to reduce rounding/truncating issues.

相关文章