时间戳

首页 > 解决方案 > 如何使用 MySQL 过滤所需的行

mysql - 如何使用 MySQL 过滤所需的行

问题描述

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

范围

| 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) 状态='未使用'的行

(b) 没有重叠的行

在发现范围重叠后，您知道如何避免/过滤 (a) 和 (b) 吗？

标签： mysql

解决方案

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

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;

推荐阅读