MSSQL获取时间差异小于X的行

I have a table with an ID, a CHAR and a DATETIME field. Now I want to get all rows, which are having a DATEDIFF of 5 minutes or less.

我有一个具有ID、CHAR和DATETIME字段的表。现在我想要得到所有的行，它们的DATEDIFF只有5分钟或更少。

---

Sample data for reference:

样本数据供参考:

  ID2    CHA       Timer
   1       B      2018-03-06 11:31:39
   2       S      2018-03-06 11:33:39
   3       B      2018-03-06 11:39:39
   4       S      2018-03-06 11:45:39
   5       B      2018-03-06 11:46:39
   6       S      2018-03-06 11:47:39
   7       B      2018-03-06 11:48:39
   8       S      2018-03-06 11:50:39
   9       B      2018-03-06 11:51:39
   10      S      2018-03-06 11:59:39

Desired output:

期望的输出:

  ID2    CHA       Timer
   1       B      2018-03-06 11:31:39
   2       S      2018-03-06 11:33:39
   4       S      2018-03-06 11:45:39
   5       B      2018-03-06 11:46:39
   6       S      2018-03-06 11:47:39
   7       B      2018-03-06 11:48:39
   8       S      2018-03-06 11:50:39
   9       B      2018-03-06 11:51:39

My current query is this:

我目前的问题是:

select *
from t t1
inner join t t2
on t1.ID = t2.ID
where datediff(minute, t1.timer, t2.timer)<=5

Sadly, this one returns the same entries multiple times. I think that this happens because of the INNER JOIN, but I can't tell for sure.

遗憾的是，这个函数多次返回相同的条目。我认为这是因为内在的联结，但我不能肯定。

How do I get the desired result?

我如何得到想要的结果?

---

Sqlfiddle to test it for yourself.

为自己测试。

4 个解决方案

#1

UPDATE

更新

Here is a new solution using the updated data.

这里有一个使用更新数据的新解决方案。

SELECT  *
  FROM  t AS t1
  WHERE EXISTS
      ( SELECT  1
          FROM  t AS t2
          WHERE ( t1.timer <= DATEADD( MINUTE, 5, t2.timer )
                  OR t1.timer >= DATEADD( MINUTE, -5, t2.timer ))
                AND t1.id <> t2.id)
;

This returns any row with another row occurring within 5 minutes before or after it. This should be able to use an index on the timer column if you're running this query with large volumes of data.

它返回在它之前或之后5分钟内发生另一行的任何行。如果您正在使用大量数据运行这个查询，那么它应该能够在timer列上使用索引。

OLD OUTDATED ANSWER

旧的过时的回答

You were very close. You needed to join on the character field except when the ID matches.

你是非常接近。除了ID匹配之外，您需要在字符字段上进行连接。

select t2.*
from t t1
inner join t t2
on t1.cha = t2.cha
and t1.id <> t2.id
where datediff(minute, t1.timer, t2.timer) <=5
order by t2.id;

#2

You can use LEAD and LAG window functions:

你可以使用前置和滞后窗口功能:

select id, cha, timer
from (
  select id, cha, timer,   
         COALESCE(datediff(minute,                   
                           lag(timer) over (order by id),
                           timer) 
                  , 10) prev_diff,
         COALESCE(datediff(minute, 
                           timer, 
                           lead(timer) over (order by id))
                  , 10) next_diff
   from t) as x
where prev_diff <= 5 or next_diff <= 5 

LEAD is used to get the timer value of the next record, whereas LAG is used to get the value of the previous record. If the difference between the current value and either of these two values is equal to or less than 5, then you have a match.

LEAD用于获取下一个记录的计时器值，而LAG用于获取前一个记录的值。如果当前值和这两个值之间的差值等于或小于5，那么就有一个匹配项。

Demo here

演示

Update:

更新:

If id field cannot be used to determine row order, then you can use a number generated by ROW_NUMBER instead:

如果不能使用id字段来确定行顺序，那么可以使用ROW_NUMBER生成的数字来代替:

;with t_rn AS (
   select id, cha, timer,
          row_number() over (order by timer) as rn
   from t
)
select id, cha, timer
from (
   select id, cha, timer,   
          coalesce(datediff(minute,                   
                            lag(timer) over (order by rn),
                            timer) 
                   , 10) prev_diff,
          coalesce(datediff(minute, 
                            timer, 
                            lead(timer) over (order by rn))
                   , 10) next_diff
   from t_rn) as x
where  prev_diff <= 5 or next_diff <= 5 

Demo here

演示

Thanks to @Vladimir, who could see the obvious where I couldn't, the above query can be simplified to:

感谢@Vladimir，谁能看到我不能看到的明显的地方，上面的查询可以简化为:

select id, cha, timer
from (
   select id, cha, timer,   
          coalesce(datediff(minute,                   
                            lag(timer) over (order by timer),
                            timer) 
                   , 10) prev_diff,
          coalesce(datediff(minute, 
                            timer, 
                            lead(timer) over (order by timer))
                   , 10) next_diff
   from t_rn) as x
where  prev_diff <= 5 or next_diff <= 5 

#3

this should do the trick

这应该能达到目的

select distinct t1.*
from t t1
inner join t t2
on t1.ID <> t2.ID
where datediff(minute, t1.timer, t2.timer) between -5 and 5

#4

Well, you can CROSS JOIN instead of INNER JOIN.

你可以交叉连接而不是内部连接。

select *
from t t1
cross join t t2
where 
    ABS(datediff(minute, t1.timer, t2.timer))<=5
    AND t1.id < t2.id

This will give all possible pairs of rows with a time difference of less than 5 minutes.

这将提供所有可能的时间间隔小于5分钟的行对。

t1.id < t2.id is needed to return only one instance of each pair.

t1。id < t2。需要id只返回一对中的一个实例。

If you are not interested in pairs as such, then, all you need is take each side of the pair and put them into one list. UNION will remove duplicates.

如果你对结对不感兴趣，那么，你所需要的就是把结对的每一边都放到一个列表中。联盟将删除重复值。

WITH
CTE_Pairs
AS
(
  select
    T1.id AS id1
    ,T1.cha AS cha1
    ,T1.timer AS timer1
    ,T2.id AS id2
    ,T2.cha AS cha2
    ,T2.timer AS timer2
  from t t1
  cross join t t2
  where 
      ABS(datediff(second, t1.timer, t2.timer)) <= 5*60
      AND t1.id < t2.id
)
SELECT 
  id1 AS id
  ,cha1 AS cha
  ,timer1 AS timer
FROM CTE_Pairs

UNION

SELECT 
  id2 AS id
  ,cha2 AS cha
  ,timer2 AS timer
FROM CTE_Pairs

ORDER BY id
;