SQL问题：从记录到记录获取基于datediff的记录

Ok, got a tricky one here... If my data looks like this:

好的,这里有一个棘手的...如果我的数据看起来像这样:

Table1

ID  Date_Created 
1   1/1/2009
2   1/3/2009
3   1/5/2009
4   1/10/2009
5   1/15/2009
6   1/16/2009

How do I get the records that are 2 days apart from each other? My end result set should be rows 1-3, and 5-6. Thanks!

如何获取彼此相隔2天的记录?我的最终结果集应该是1-3行和5-6行。谢谢!

7 个解决方案

#1

select distinct t1.*
from Table1 t1
inner join Table1 t2 
    on abs(cast(t1.Date_Created - t2.Date_Created as float)) between 1 and 2

#2

SELECT l.*
FROM Table1 l
INNER JOIN Table1 r ON DATEDIFF(d, l.Date_Created, r.Date_Created) = 2
      AND r.Date_Created = (SELECT TOP 1 * FROM Table1 WHERE Date_Created > l.Date_Created ORDER BY Date_Create)

#3

-- what does this give you?

- 这给你带来了什么?

select DISTINCT t1.id, t1.date_created, t2.id, t2.date_created from table1 t1, table1 t2 where datediff(dd,t1.date_created,t2.date_created) = 2 AND t1.id != t2.id ORDER BY t1.id;

选择DISTINCT t1.id,t1.date_created,t2.id,t2.date_created from table1 t1,table1 t2 where datediff(dd,t1.date_created,t2.date_created)= 2 AND t1.id!= t2.id ORDER BY t1 。ID;

#4

Would this work?

这会有用吗?

select t1.id, t2.id 
  from table1 t1 
  join table1 t2 
    on t2.date_created - t1.date_created <= 2

#5

I might suggest using programming code to do it. You want to collect groups of rows (separate groups). I don't think you can solve this using a single query (which would give you just one set of rows back).

我可能会建议使用编程代码来完成它。您想要收集行组(单独的组)。我不认为你可以使用单个查询来解决这个问题(它只会给你一组行回复)。

#6

If you want to get the rows which are WITHIN 'N' days apart, you can try this:

如果你想获得与'N'天相隔的行,你可以试试这个:

select t1.date_created, t2.date_created 
from table1 t1, table1 t2 
where t1.id <> t2.id and 
      t2.date_created-t1.date_created between 0 and N;

for exmaple, as you said, if you want to get the rows which are WITHIN 2 days a part, you can use the below:

例如,正如你所说,如果你想获得2天以内的行,你可以使用下面的代码:

select t1.date_created,t2.date_created 
from table1 t1, table1.t2 
where t1.id <> t2.id and 
      t2.date_created-t1.date_created between 0 and 2;

I hope this helps....

我希望这有帮助....

Regards, Srikrishna.

#7

A cursor will be fastest, but here is a SELECT query that will do it. Note that for "up to N" days apart instead of 2 you'll have to replace the table Two with a table of integers from 0 to N-1 (and the efficiency will get worse).

游标将是最快的,但这是一个SELECT查询,它将执行它。请注意,对于“最多N天”而不是2天,您必须将表格2替换为0到N-1之间的整数表(并且效率会变差)。

I'll admit it's not entirely clear what you want, but I'm guess you want the ranges of rows that contain at least two rows in all and within which the successive rows are at most 2 days apart. If dates increase along with IDs, this should work.

我承认它并不完全清楚你想要什么,但我猜你想要的行数范围至少包含两行,其中连续的行最多相隔2天。如果日期随ID增加,这应该有效。

with Two as (
  select 0 as offset union all select 1 
), r2(ID, Date_Created_o, dr) as (
  select
    ID, Date_Created+offset,
    Date_Created + offset - dense_rank() over (
      order by Date_Created+offset
    ) from r cross join Two
)
  select
    min(ID) as start, max(ID) as finish
  from r2
  group by dr
  having min(ID) < max(ID)
  order by dr;

#1

select distinct t1.*
from Table1 t1
inner join Table1 t2 
    on abs(cast(t1.Date_Created - t2.Date_Created as float)) between 1 and 2

#2

SELECT l.*
FROM Table1 l
INNER JOIN Table1 r ON DATEDIFF(d, l.Date_Created, r.Date_Created) = 2
      AND r.Date_Created = (SELECT TOP 1 * FROM Table1 WHERE Date_Created > l.Date_Created ORDER BY Date_Create)

#3