MSSQL获取时差低于X的行

更新

下面是一个使用更新数据的新解决方案。

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)
;

这将返回在其前后5分钟内出现另一行的任何行。这应该能够在 timer 列,如果您使用大量数据运行此查询。

过时的旧答案

你非常接近。除非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;

您可以使用 LEAD 和 LAG 窗口功能:

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 

潜在客户 用于获取 timer 的值 下一个 记录,鉴于 滞后 用于获取 以前的 记录如果当前值与这两个值之间的差值等于或小于 5 ,那么您就有了一个匹配项。

Demo here

更新时间:

如果 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

感谢@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 

这应该可以做到

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

嗯,你可以 CROSS JOIN 而不是 INNER JOIN 。

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

这将使所有可能的行对的时差小于5分钟。

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

如果你对这对搭档不感兴趣,那么你所需要的就是把这对搭档的每一边都放在一个列表中。 UNION 将删除重复项。

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
;