简单来说,如果一个查询告诉我A与B重叠,那么我就不需要它再告诉我B也与A重叠,因为它们彼此重叠。
所以我正在尝试在SQL中使用自连接来选择“DISTINCT”重叠部分。
举个例子,这里有一个简单的SQL fiddle,用来展示包含性重叠选择(http://sqlfiddle.com/#!9/7af84f/1)。
详细地说...
假设我有一个表格,其中包含名称(char)、d1(int)、d2(int),其模式如下。这里的d1和d2表示某个间隔的开始和结束,可能与同一表格中的另一个间隔重叠。
给定这个表格,我填充了一些值。
我的问题是:
如何修改SQL语句以只获取四行“DISTINCT”或“单向”重叠?
即此结果...
所以我正在尝试在SQL中使用自连接来选择“DISTINCT”重叠部分。
举个例子,这里有一个简单的SQL fiddle,用来展示包含性重叠选择(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) 的重叠,因为第二行已经显示了 A 和 C 的重叠 }
- D(1,8) { 不显示任何新内容,因为我们已经知道 A(1,10) 和 C(5,10) 的重叠情况 }
- E(13,15) { 不显示任何新内容,因为我们已经知道 B(12,20) 的情况 }
- F(25,30) { 没有重叠,不显示任何内容 }
basetable.letter <> overlaptable.letter
更改为basetable.letter < overlaptable.letter
。这还可以将查询速度提高多达50%。(这就是我现在能看到的,正是@Strawberry使用的话语)。 - Solarflarebasetable.letter {< | > }overlaptable.letter
之外,还有其他可能的选项,如basetable.d1 > overlaptable.d1'
,basetable.d2 < overlaptable.d2
。 - Serg