如何用MySQL过滤所需行

Question

亲爱的各位：在获取下表中所有重叠的范围后：

范围

| 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)吗？

Answer 1

我不能绝对肯定我是否理解你是正确的，但对我来说，你似乎正在寻找这样的东西？

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;