本文介绍了TSQL 查询返回相距 5 分钟之内的所有行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想返回表中相距在 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.
这篇关于TSQL 查询返回相距 5 分钟之内的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！