时间戳

首页 > 解决方案 > 使用 MySQL 查找连续范围

mysql - 使用 MySQL 查找连续范围

问题描述

我有一个不同客户（customer_id）的值（数字）表：

CREATE TABLE dev.customer_resources (
  id int(11) NOT NULL AUTO_INCREMENT,
  customer_id int(11) DEFAULT NULL,
  value int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

我想获取特定客户的数字范围。所以，如果表数据是：

======================
|id|customer_id|value|
======================
| 1|         21|    1|
----------------------
| 2|         21|    2|
----------------------
| 3|         21|    3|
----------------------
| 4|         21|   20|
----------------------
| 5|         21|   21|
----------------------
| 6|         21|   22|
----------------------
| 7|         22|    5|
----------------------

对于 customer_id=21，我会得到类似这样的结果：

==========
start |end
==========
|    1| 3|
----------
|   20|22|
----------

对于 customer_id=22 类似这样的：

==========
start |end
==========
|    5| 5|
----------

搜索解决方案后，我找到了以下代码：

select l.value as start,
    (
        select min(a.value) as value
        from customer_resources as a
            left outer join customer_resources as b on a.value = b.value - 1
        where b.value is null
            and a.value >= l.value
    ) as end
from customer_resources as l
    left outer join customer_resources as r on r.value = l.value - 1
where r.value is NULL;

这几乎可以满足我的需要，但不包括将结果限制为特定的 customer_id。我尝试将 customer_id 添加到查询中，但我不太确定在哪里添加它，因为它所做的只是破坏功能。

标签： mysqlsqlgaps-and-islands

解决方案

LukStorms 解决方案似乎是正确的事情 - 它似乎比严格必要的要复杂一些......

这是主题的变体...

    DROP TABLE IF EXISTS my_table;

    CREATE TABLE my_table
    (id SERIAL PRIMARY KEY
    ,customer_id int NOT NULL
    ,value int NOT NULL
    );

    INSERT INTO my_table VALUES
    (1,         21,    1),
    (2,         21,    2),
    (3,         21,    3),
    (4,         21,   20),
    (5,         21,   21),
    (6,         21,   22),
    (7,         22,    5);

    SELECT customer_id
         , MIN(value) start
         , MAX(value) end 
      FROM 
         ( SELECT *
                , CASE WHEN value = @prev+1 THEN @i:=@i ELSE @i:=@i+1 END grp
                , @prev:=value prev
             FROM my_table
                , (SELECT @prev:= null,@i:=0) vars 
            ORDER 
               BY customer_id
                , id
         ) x 
     GROUP 
        BY customer_id,grp;
    +-------------+-------+------+
    | customer_id | start | end  |
    +-------------+-------+------+
    |          21 |     1 |    3 |
    |          21 |    20 |   22 |
    |          22 |     5 |    5 |
    +-------------+-------+------+

推荐阅读