在mysql中选择连续的记录块 [英] Selecting contiguous block of records in mysql
问题描述
我在MySql 5中有一个电话号码表.简单的结构是
I have a table in MySql 5 of phone numbers. The simple structure is
Accounts
id varchar(32) NOT NULL
记录如下
27100070000
27100070001
27100070002
27100070003
27100070004
27100070005
27100070008
27100070009
27100070012
27100070015
27100070016
27100070043
我需要对这些数据进行排序,并将连续的数字块分组为数字范围.我愿意在C#LINQ中实现该解决方案,但是服务器端MySql是一等奖.在MySql中有没有一种方法可以汇总这些数据,以便输出如下所示?
I need to sort through this data and group contiguous blocks of numbers into number ranges. I'm open to implementing the solution in C# LINQ but server-side MySql is first prize. Is there a way in MySql to get this data summarised so that the output is as below?
Start | End
-------------------------
27100070000 | 27100070005
27100070008 | 27100070009
27100070012 | 27100070015
27100070016 | NULL
27100070043 | NULL
推荐答案
有一个简单的技巧可以将连续的条目折叠成一个组.如果按(row_number-entry)分组,则连续的条目将以同一组结束.这是一个演示我的意思的示例:
There is a simple trick to collapse consecutive entries into a single group. If you group by (row_number - entry), the entries that are consecutive will end up in the same group. Here is an example demonstrating what I mean:
查询:
SELECT phonenum, @curRow := @curRow + 1 AS row_number, phonenum - @curRow
from phonenums p
join (SELECT @curRow := 0) r
结果:
| PHONENUM | ROW_NUMBER | PHONENUM - @CURROW |
-------------------------------------------------
| 27100070000 | 1 | 27100069999 |
| 27100070001 | 2 | 27100069999 |
| 27100070002 | 3 | 27100069999 |
| 27100070003 | 4 | 27100069999 |
| 27100070004 | 5 | 27100069999 |
| 27100070005 | 6 | 27100069999 |
| 27100070008 | 7 | 27100070001 |
| 27100070009 | 8 | 27100070001 |
| 27100070012 | 9 | 27100070003 |
| 27100070015 | 10 | 27100070005 |
| 27100070016 | 11 | 27100070005 |
| 27100070040 | 12 | 27100070028 |
请注意,连续的条目都对PHONENUM - @CURROW
具有相同的值.如果我们在该列上进行分组,然后选择分钟和分钟每个组的最大值,您都有摘要(一个例外:如果需要START = END,则可以用NULL
替换END值):
Notice how the entries that are consecutive all have the same value for PHONENUM - @CURROW
. If we group on that column, and select the min & max of each group, you have the summary (with one exception: you could replace the END value with NULL
if START = END if that's a requirement):
查询:
select min(phonenum), max(phonenum) from
(
SELECT phonenum, @curRow := @curRow + 1 AS row_number
from phonenums p
join (SELECT @curRow := 0) r
) p
group by phonenum - row_number
结果:
| MIN(PHONENUM) | MAX(PHONENUM) |
---------------------------------
| 27100070000 | 27100070005 |
| 27100070008 | 27100070009 |
| 27100070012 | 27100070012 |
| 27100070015 | 27100070016 |
| 27100070040 | 27100070040 |
演示: http://www.sqlfiddle.com/#!2/59b04/5
这篇关于在mysql中选择连续的记录块的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!