SQL 从同一个表中检测重叠的日期范围

Detect overlapping date ranges from the same table

提问by Naeem Sarfraz

I have a table with the following data

我有一个包含以下数据的表

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  14/01/2010  S
02    15/01/2010  31/01/2010  S
03    05/01/2010  06/01/2010  A

And want to get the following results

并希望得到以下结果

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  14/01/2010  S
03    05/01/2010  06/01/2010  A

Any ideas on where to start? A lot of the reading I've done suggests I need to create entries and for each day and join on matching days, is this the only way?

关于从哪里开始的任何想法？我所做的很多阅读都表明我需要为每一天创建条目并在匹配的日子加入，这是唯一的方法吗？

回答by SWeko

If you already have entries for each day that should work, but if you don't the overhead is significant, and if that query is used often, if will affect performance.

如果您已经有应该工作的每一天的条目，但如果您没有，则开销很大，并且如果经常使用该查询，则是否会影响性能。

If the data is in this format, you can detect overlaps using simple date arithmetic, because an overlap is simply one interval starting after a given interval, but before the given is finished, something like

如果数据采用这种格式，您可以使用简单的日期算法检测重叠，因为重叠只是在给定间隔之后开始的一个间隔，但在给定完成之前，类似于

select dr1.* from date_ranges dr1
inner join date_ranges dr2
on dr2.start > dr1.start -- start after dr1 is started
  and dr2.start < dr1.end -- start before dr1 is finished

If you need special handling for interval that are wholly within another interval, or you need to merge intervals, i.e.

如果您需要对完全在另一个间隔内的间隔进行特殊处理，或者您需要合并间隔，即

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  20/01/2010  S
02    15/01/2010  31/01/2010  S

yielding

屈服

Start       End         Type
=====       ===         ====
01/01/2010  31/01/2010  S

you will need more complex calculation.

您将需要更复杂的计算。

In my experience with this kind of problems, once you get how to do the calculation by hand, it's easy to transfer it into SQL :)

根据我对此类问题的经验，一旦您掌握了如何手动进行计算，就很容易将其转换为 SQL :)

回答by bobfet1

When I needed to compare two time spans in SQL for overlap, here are the four scenarios I could think of:

当我需要比较 SQL 中的两个时间跨度的重叠时，我可以想到以下四种情况：

1. Span1 start is between Span2 start and Span2 end
2. Span1 end is between Span2 start and Span2 end
3. Span1 start and end are both between Span2 start and Span2 end
4. Span2 start and end are both between Span1 start and Span1 end

1. Span1 start 介于 Span2 start 和 Span2 end 之间
2. Span1 end 介于 Span2 start 和 Span2 end 之间
3. Span1 start 和 end 都在 Span2 start 和 Span2 end 之间
4. Span2 start 和 end 都在 Span1 start 和 Span1 end 之间

Here is the OR statement I created to capture these scenarios (in my case Oracle SQL):

这是我为捕获这些场景而创建的 OR 语句（在我的示例中为 Oracle SQL）：

and (
    s1.start between s2.start and s2.end
    OR
    s1.end between s2.start and s2.end
    OR
    s2.start between s1.start and s1.end
)

回答by Fionnuala

Perhaps:

也许：

SELECT A.PKey, A.Start, A.End, A.Type
FROM calendar AS A, calendar AS B
WHERE (p.pkey<>a.pkey
AND b.start>=a.start
AND b.end<=a.end)
OR (b.pkey<>a.pkey
AND b.start<=a.start
AND b.end>=a.end)

回答by davek

select A.*
from MyTable A
inner join MyTable B
on (B.start <= A.end)
and (B.end >= A.start)

or something like that (assuming dates are not nullable and equal dates count as an overlap).

或类似的东西（假设日期不可为空并且相等的日期算作重叠）。

回答by Eminem

I had to do a very similar thing for to stop duplicate holiday being entered into a table. it was in access and written to a temptable on input so had to query it in VBA SQL:

我不得不做一个非常相似的事情来阻止重复的假期被输入到表格中。它可以访问并写入输入的临时表，因此必须在 VBA SQL 中查询它：

 stCommandText = "SELECT " _
                    & "* " _
                    & "FROM " _
                    & "TableName a, " _
                    & "TableName b " _
                    & "WHERE " _
                    & "a.ID = b.ID " _
                    & "AND a.Startdate >= b.Startdate AND a.StartDate <= b.EndDate " _
                    & "AND a.AutoNo <> b.AutoNo "

回答by fujiiface

We've all needed this kind of overlapping predicate in our queries for quite some time and I think I've found a really simple solution here.

很长一段时间以来，我们都需要在查询中使用这种重叠谓词，我想我在这里找到了一个非常简单的解决方案。

In my application, as an example, I have policies that have the same Policy Number but maybe the Policy Description changes from one fiscal year to the next. When a user is entering a new record (same Policy Number, different Policy Description), I needed a way to tell if that policy already exists for the specified time range. If the new Policy Effective/Expiration dates overlap with whatever is already in the database, I needed to error out and tell the user why their input was not correct.

例如，在我的申请中，我的保单具有相同的保单编号，但保单说明可能会从一个财政年度更改为下一个财政年度。当用户输入新记录（相同的策略编号，不同的策略描述）时，我需要一种方法来判断该策略是否在指定的时间范围内已经存在。如果新的政策生效/到期日期与数据库中已有的日期重叠，我需要出错并告诉用户为什么他们的输入不正确。

To do this, I went with the following predicate statement:

为此，我使用了以下谓词语句：

AND @_expiration >= EffectiveDate AND ExpirationDate >= @_effective

Hopefully someone else finds this as useful as I have.

希望其他人发现这和我一样有用。

回答by Dmitry Polushkin

In MySQL you basically need:

在 MySQL 中，您基本上需要：

SELECT COUNT(*) FROM date_ranges AS A, date_ranges AS B WHERE A.id <> B.id AND A.id > B.id AND A.end_at > B.start_at AND B.end_at > A.start_at

SELECT COUNT(*) FROM date_ranges AS A, date_ranges AS B WHERE A.id <> B.id AND A.id > B.id AND A.end_at > B.start_at AND B.end_at > A.start_at

>in the second and the third statement can be replaced with >=to follow includes matching.

>在第二条和第三条语句中可以替换>=为follow包括匹配。

This topic is related to the "Allen's Interval Algebra" and there are some more reading on this can be found by those links:

该主题与“艾伦区间代数”有关，可以通过以下链接找到更多关于此的阅读：

• http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html
• http://salman-w.blogspot.com.es/2012/06/sql-query-overlapping-date-ranges.html

• http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html
• http://salman-w.blogspot.com.es/2012/06/sql-query-overlapping-date-ranges.html

回答by ozmike

BTW - If you don't have a unique id , against your dates you can do this is oracle..FYI

顺便说一句 - 如果你没有唯一的 id ，根据你的日期，你可以这样做是 oracle..FYI

with date_ranges
as
(
SELECT 
     rownum as pkey,
    date_ranges.*
FROM  date_ranges
) 
select 
dr1.* 
from 
date_ranges dr1 , date_ranges dr2
where  dr1.pkey > dr2.pkey
AND dr1.end_dt >= dr2.start_dt 
AND dr2.end_dt >= dr1.start_dt

回答by kuldeep

Sql='SELECT task_id, task_start_date, task_due_dateFROM (wba_task) WHERE (task_start_date<="2016-07-13" AND task_due_date>="2016-07-25") OR (task_due_dateBETWEEN "2016-07-13" and "2016-07-25")';

Sql='SELECT task_id, task_start_date, task_due_dateFROM ( wba_task) WHERE ( task_start_date<="2016-07-13" AND task_due_date>="2016-07-25") OR ( task_due_dateBETWEEN "2016-07-13" 和 "2016-07-25") ';

Codeigniter Query is below.

Codeigniter 查询如下。

$fromdaysDate="2016-07-13";//changed date
$todaysDate="2016-07-25";//changed date
$this->db->select('task_id,task_start_date, task_due_date'); 
$this->db->where('task_start_date <="'.date('Y-m-d', strtotime($fromdaysDate)).'"');
$this->db->where('task_due_date >="'.date('Y-m-d', strtotime($todaysDate)).'"');    
$this->db->or_where('task_due_date BETWEEN "'. date('Y-m-d', strtotime($fromdaysDate)). '" and "'. date('Y-m-d', strtotime($todaysDate)).'"');   
$alltask=$this->db->get('wba_task')->result_array();
echo $this->db->last_query();

get all overlap data form database....

获取所有重叠数据表单数据库....