查找连续索引，直到某些字段发生更改 [英] Find contiguous index until certain field changes

问题描述

我在MySQL服务器中有一些数据，其索引号从0到15，另外两个字段包含名称和地址。

I have some data in a MySQL server that has an index number from 0 to 15, and two other fields that contain a name and an address.

Name    Address Index
-----   ------- --
Test    0x0100  0
Test    0x0100  1
Test    0x0100  2
Test    0x0100  3
Test    0x0100  4
Test2   0x0100  5
Test2   0x0100  6
Test2   0x0100  7
Test    0x0100  8
Test    0x0100  9
Test    0x0100  10
Test3   0x0100  11
Test3   0x0100  12
Test    0x0100  13
Test    0x0100  14
Test    0x0100  15

数据按地址排序，然后按索引排序。

The data is sorted by address and then index.

我想总结一下每个索引时具有索引范围的数据，如下所示：

I would like to summarize this data with the range of indices when each index, like the following:

Name    Address Start   End
-----   ------- -----   ----
Test    0x0100  0       4
Test2   0x0100  5       7
Test    0x0100  8       10
Test3   0x0100  11      12
Test    0x0100  13      15

在MySQL中是否可以做到这一点？或者，除了蛮力之外，是否有一种有效的算法可以完成此任务？查询返回后，可能会进行一些后期处理。

Is there a way to do this in MySQL? Alternatively, Is there an efficient algorithm to accomplish this other than brute force? Some post processing is possible after the query returns.

推荐答案

您可以在MySQL中使用变量进行操作：

You can do this in MySQL, using variables:

select name, address, min(index) as start_index, max(index) as end_index
from (select t.*,
             (@grp := if(@name = name, @grp,
                        if(@name := name, @grp + 1, @grp + 1)
                       )
             ) as grp
      from t cross join
           (select @grp := 0, @name := '') params
      order by index
     ) t
group by name, address, grp;

这里是工作版本。

这篇关于查找连续索引，直到某些字段发生更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！