选择记录在MySQL连续块 [英] Selecting continuous block of records in mysql
问题描述
我有电话号码的MySQL 5表。简单的结构是
帐户
ID VARCHAR（32）NOT NULL
的记录如下
27100070000
27100070001
27100070002
27100070003
27100070004
27100070005
27100070008
27100070009
27100070012
27100070015
27100070016
27100070043
我需要通过数这一数据和组连续的块进行排序成数范围。我愿意实施在C＃中的LINQ，但服务器端的解决方案，MySQL是一等奖。有没有办法在MySQL来获取这些数据汇总，以便输出如下？
开始|结束
-------------------------
27100070000 | 27100070005
27100070008 | 27100070009
27100070012 | 27100070015
27100070016 |空值
27100070043 |空值
有一个简单的技巧崩溃连续输入到一个组。如果GROUP BY（ROW_NUMBER - 项），这是连续的项将在同一组。下面是一个例子证明了我的意思：
查询
SELECT PHONENUM，@curRow：= @curRow + 1 ROW_NUMBER，PHONENUM - @curRow
从phonenums p
加入（选择@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 。如果我们在该栏目组，并选择分钟和放大器;最多各组，你有总结（有一个例外：你可以用 NULL 替换终值，如果START =结束时，如果这是一个要求）：
查询
选择分钟（PHONENUM），MAX（PHONENUM）从
（
SELECT PHONENUM，@curRow：= @curRow + 1 ROW_NUMBER
从phonenums p
加入（选择@curRow：= 0）R
）p
按PHONENUM - ROW_NUMBER
结果
| MIN（PHONENUM）| MAX（PHONENUM）|
---------------------------------
| 27100070000 | 27100070005 |
| 27100070008 | 27100070009 |
| 27100070012 | 27100070012 |
| 27100070015 | 27100070016 |
| 27100070040 | 27100070040 |
演示： http://www.sqlfiddle.com/#!2/59b04/5
I have a table in MySql 5 of phone numbers. The simple structure is
Accounts
id varchar(32) NOT NULL
The records are as follows
27100070000
27100070001
27100070002
27100070003
27100070004
27100070005
27100070008
27100070009
27100070012
27100070015
27100070016
27100070043
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
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:
Query:
SELECT phonenum, @curRow := @curRow + 1 AS row_number, phonenum - @curRow
from phonenums p
join (SELECT @curRow := 0) r
Results:
| 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 |
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):
Query:
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
Results:
| MIN(PHONENUM) | MAX(PHONENUM) |
---------------------------------
| 27100070000 | 27100070005 |
| 27100070008 | 27100070009 |
| 27100070012 | 27100070012 |
| 27100070015 | 27100070016 |
| 27100070040 | 27100070040 |
Demo: http://www.sqlfiddle.com/#!2/59b04/5
这篇关于选择记录在MySQL连续块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
