问题描述
我想返回表中相距在 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.