mysql - 将值范围与另一个值范围进行比较 mysql

Question

我在 MySQL 数据库中有一个表(shrewd_db.threshold)，我在其中执行了以下代码

select  id_indicator, id_threshold, activation_begin_value, activation_end_value 
from shrewd_db.threshold
where active =1
group by id_indicator, id_threshold;

我的输出如下:

 id_indicator   id_threshold    activation_begin_value  activation_end_value 

   2                5                   4                 58
   2                6                   3                  3
   2                7                   1                  2
   2                8                   0                  0
   4               13                   0                4.5
   4               15                 4.1                5.5
   4               16                5.51                  6
   4               17                6.01                100

每个 id_indicator 有四个 id_threshold 范围。

现在，为了进行数据验证检查，我需要找出每个指标 id_threshold(activation_begin_value 和activation_end_value)不落入另一个阈值。

我想要的输出应该是这样的:

 id_indicator   id_threshold    activation_begin_value  activation_end_value 


   4               13                   0                4.5
   4               15                 4.1                5.5

其中 id_indicator (4) 两个 id_threshold(13,15) 值相互落在。主要标识阈值不能重叠

Answer 1

我认为您的意思是激活期不能重叠，对吧？

然后检查这一点的查询将如下所示:

select distinct t1.* from threshold t1, threshold t2 where
  t1.id_indicator = t2.id_indicator and
  t1.id_threshold <> t2.id_threshold and
    (  t1.activation_begin_value BETWEEN t2.activation_begin_value and 
       t2.activation_end_value
    OR t1.activation_end_value BETWEEN t2.activation_begin_value and 
       t2.activation_end_value
    )

使用以下架构测试查询:

CREATE TABLE threshold
    (`id_indicator` int, `id_threshold` int, `activation_begin_value` numeric(5,2), `activation_end_value` numeric(5,2))
;

INSERT INTO threshold
    (`id_indicator`, `id_threshold`, `activation_begin_value`, `activation_end_value`)
VALUES
    (2, 5, 4, 58),
    (2, 6, 3, 3),
    (2, 7, 1, 2),
    (2, 8, 0, 0),
    (4, 13, 0, 4.5),
    (4, 15, 4.1, 5.5),
    (4, 16, 5.51, 6),
    (4, 17, 6.01, 100)
;

并产生以下结果:

id_indicator|id_threshold|activation_begin_value | activation_end_value
------------|------------|-----------------------|---------------------
           4|          15|                    4.1|                  5.5
           4|          13|                      0|                  4.5