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){不要顯示任何内容，因為沒有重疊}