使用mysql查找连续范围

azpvetkf 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(383)

我有一个不同客户（客户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\u id=21，我将得到如下结果：

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

对于客户\u 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\u id。我尝试将customer\u id添加到查询中，但我不太确定要将其添加到何处，因为它所做的只是中断功能。

sql mysql gaps-and-islands

来源：https://stackoverflow.com/questions/51271387/find-contiguous-ranges-with-mysql

2条答案

按热度按时间

wmomyfyw1#

这似乎是一个正确的解决方案-它只是似乎有点复杂，比严格必要的。。。
这是主题的变化。。。

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 |
    +-------------+-------+------+

赞(0）回复(0）举报 2021-06-21

evrscar22#

这似乎是一个缺口和孤岛问题。
这里有一个老式的mysql特定解决方案，它使用变量。
通过比较上一个值和客户id可以找到孤岛的开始。

SELECT 
 customer_id, 
 value_start as `start`, 
 max(val) as `end`
FROM 
(
    SELECT 
      CASE 
      WHEN `value` = @prev_val + 1 AND customer_id = @prev_grp
      THEN @start_val:= @start_val 
      ELSE @start_val:=`value`
      END AS value_start, 
      @prev_grp:=customer_id as customer_id,
      @prev_val:=`value` as val
    FROM customer_resources
    CROSS JOIN (SELECT @prev_val:=NULL, @start_val:=NULL, @prev_grp:=NULL) AS v
    ORDER BY customer_id, `value`
) AS q 
GROUP BY customer_id, value_start;

赞(0）回复(0）举报 2021-06-21

我来回答

使用mysql查找连续范围

2条答案

相关问题

热门标签

最新问答