mysql时间段重叠_MySQL - 如何选择'DISTINCT'重叠时段(日期或数字范围)

简单地说，如果查询告诉我A重叠B，那么我不需要它也告诉我B重叠A，因为它们彼此重叠。

所以我想在sql中使用自联接来选择'DISTINCT'重叠。

为了说明，这里是我写的一个简单的SQL小提琴，用于显示包含重叠选择(http://sqlfiddle.com/#!9/7af84f/1)

详细...

假设我有一个名称(char)，d1(int)，d2(int)的表格，其架构如下。这里，d1和d2表示可能与同一个表中的另一个区间重叠的某个区间的开始和结束。

CREATE TABLE test (

letter char ,

d1 int ,

d2 int

) ;鉴于这张表，我填写了一些值

INSERT INTO test (letter,d1,d2)

VALUES

('A', 2, 10), -- overlaps C and D

('B', 12, 20), -- overlaps E

('C', 5, 10), -- overlaps A and D

('D', 1, 8), -- overlaps A and C

('E', 13, 15), -- overlaps B

('F', 25, 30); -- doesn't overlap anything并运行以下使用自联接的查询来正确查找其中一行中的d1和d2与其他行中的d1和d2重叠的行。

-- selects all records that overlap in the range d1 - d2 inclusive

-- (excluding the implicit overlap between a record and itself)

-- The results are sorted by letter followed by d1

SELECT

basetable.letter as test_letter,

basetable.d1,

basetable.d2,

overlaptable.letter as overlap_letter,

overlaptable.d1 as overlap_d1,

overlaptable.d2 as overlap_d2

FROM

test as basetable,

test as overlaptable

WHERE

-- there is an inclusive overlap

basetable.d1 <= overlaptable.d2 and basetable.d2 >= overlaptable.d1

AND

-- the row being checked is not itsself

basetable.letter <> overlaptable.letter

AND

basetable.d1 <> overlaptable.d1

AND

basetable.d2 <> overlaptable.d2

ORDER BY

basetable.letter,

basetable.d1这正确地给了我以下内容，显示所有6个版本的重叠，例如，左边的列表示A重叠C，另一行显示C重叠A(注意sqlfiddle似乎不理解字段别名，因此我的列标题不同)

test_letter d1 d2 overlap_letter overlap_d1 overlap_d2

A 2 10 D 1 8

B 12 20 E 13 15

C 5 10 D 1 8

D 1 8 A 2 10

D 1 8 C 5 10

E 13 15 B 12 20我的问题是这样的：

我怎样才能改变SQL来获得四行'DISTINCT'或'单向'重叠？

即这个结果...

test_letter d1 d2 overlap_letter overlap_d1 overlap_d2

A 2 10 D 1 8

A 2 10 C 5 10

B 12 20 E 13 15

C 5 10 D 1 8例如：点击

根据以下推理在左侧栏中显示A，B和C的记录的结果

A(2,10)与D(1,8)和C(5,10)以及{显示这两行}重叠

B(12,20)与E(13,15){显示此行}重叠

C(5,10)与D(1,8)重叠{显示此行但不显示A(1,10)重叠，因为行2已经显示A和C重叠}

D(1,8){因为我们已经知道A(1,10)和C(5,10)}，所以不要显示任何新东西。

E(13,15){因为我们已经知道B(12,20)}，所以不会显示任何新东西。“

F(25,30){不要显示任何内容，因为没有重叠}