列出连续记录范围的有效方法

提问者：小点点

列出连续记录范围的有效方法

我摆了一张桌子:

CREATE TABLE `cn` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `type` int(3) unsigned NOT NULL,
    `number` int(10) NOT NULL,
    `desc` varchar(64) NOT NULL,
    `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB

number通常是唯一的，但不一定是唯一的。

表的大部分由具有连续number条目的行组成。

例如。

101010，101011，101012等。

我一直在试图找到一种有效的方法来列出连续数字的范围，这样我就可以很容易地找出数字“丢失”的地方。我想做的是列出开始号，结束号和连续行数。由于可能存在重复，我使用select DISTINCT（number)来避免重复。

我的运气不太好--这类问题大多涉及日期，很难一概而论。有一个查询是永远执行的，所以这是不可行的。这个答案有点接近，但不完全。它使用交叉连接，当您有数百万条记录时，这听起来像是灾难的配方。

最好的办法是什么？有些答案使用联接，我对其性能持怀疑态度。现在只有50,000行，但是在几天内将会有数百万条记录，因此性能的每一盎司都很重要。

我想到的最终伪查询是这样的:

从cn中选择DISTINCT（number），其中type=1 GROUP BY[连续...] 按编号ASC排序

共1个答案

匿名用户

这是一个空白和孤岛问题。您可以通过使用row_number()和number之间的差异定义组来解决；差距通过差额的变化来确定:

select type, min(number) first_number, max(number) last_number, count(*) no_records
from (
    select cn.*, row_number() over(order by number) rn
    from cn
    where type = 1
) c
group by type, number - rn

注意:窗口函数在MySQL8.0和Mariadb10.3以后都是可用的。

在早期版本中，可以使用会话变量模拟row_number():

select type, min(number) first_number, max(number) last_number, count(*) no_records
from (
    select c.*, @rn := @rn + 1 rn
    from (select * from cn where type = 1 order by number) c
    cross join (select @rn := 0) r
) c
group by number - rn