简而言之，如果查询告诉我 A 与 B 重叠，那么我不需要它也告诉我 B 也与 A 重叠，因为它们彼此重叠。

所以我尝试在 sql 中使用自连接来选择“不同的”重叠部分。

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

详细...

假设我有一个 name (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 以仅获得四行“不同”或“单向”重叠？

即这个结果...

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) 重叠{SHOW THIS ROW}
• 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) {不显示任何内容，因为没有重叠}

最佳答案

你可以改成不等式。而且，您还应该使用 JOIN:

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 basetable JOIN
     test overlaptable
     ON basetable.d1 <= overlaptable.d2 AND
        basetable.d2 >= overlaptable.d1
WHERE basetable.letter < overlaptable.letter  -- This is the change
ORDER BY basetable.letter, basetable.d1;

关于MySQL - 如何选择 'DISTINCT' 重叠周期(日期或数字范围)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39955820/