TSQL query to return all rows that are within 5 mi

问题:

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

The Example Table:

The KillTime is what we are querying for the within 5 mins

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

回答1:

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.

回答2:

You should join table with itself and in ON clause put:

datediff(minute, tbl1.KillTime, tbl2.KillTime) = 5 and tbl2.KillID > tbl1.KillID (or between 1 and 5 or whatever condition you need)