亲爱的各位:在获取下表中所有重叠的范围后:
范围
| Count | Status | Begin | End | Comment |
| 1 | used | 1001 | 1095 | overlaps with ranges 2, 3 |
| 2 | hold | 1005 | 1030 | overlaps with ranges 1, 3 |
| 3 | unused | 1017 | 1020 | overlaps with ranges 1, 2 |
| 4 | used | 1110 | 1125 | no overlap |
通过使用:
select r1.count, r1.begin, r1.end,
group_concat(r2.count order by r2.count) as overlaps
from ranges r1 left join
ranges r2
on r1.end >= r2.begin and
r1.begin <= r2.end and
r1.count <> r2.count
group by r1.count, r1.begin, r1.end;
我现在有一些我想避免的额外信息,例如:
(a)status='unused'的行
(b)没有重叠的行
在发现范围重叠后,您知道如何避免/过滤(a)和(b)吗?
我不能绝对肯定我是否理解你是正确的,但对我来说,你似乎正在寻找这样的东西?
select r1.count, r1.begin, r1.end,
group_concat(r2.count order by r2.count) as overlaps
from ranges r1
left join ranges r2
on r1.end >= r2.begin and
r1.begin <= r2.end and
r1.count <> r2.count and
r2.status <> 'unused'
where r1.Count IS NOT NULL and r1.status <> 'unused'
group by r1.count, r1.begin, r1.end;