>我有一个表格如下：
CREATE TABLE `table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cc` int(3) unsigned NOT NULL,
`number` int(10) NOT NULL,
`name` varchar(64) NOT NULL,
`datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
DBMS 是 Debian 9.1 上的 MariaDB 10.1.26。我一直试图让它列出连续数字的范围。通过以下查询，我能够完成此操作：
SELECT min(number) first_number, max(number) last_number, count(*) AS no_records FROM (
SELECT c.*, @rn := @rn + 1 rn
from (SELECT number FROM table WHERE cc = 1 GROUP BY number ORDER BY number) AS c
CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY number - rn ORDER BY number ASC
但是，如果我希望根据附加列中的值将项目捆绑在一起，这是行不通的。假设我希望仅当项目的值都相同时对name项目进行分组。假设这是我的数据：
INSERT INTO `table` (`id`, `cc`, `number`, `name`) VALUES
(1, 1, 12, 'Hello'),
(2, 1, 2, 'Apple'),
(3, 1, 3, 'Bean'),
(4, 1, 10, 'Hello'),
(5, 1, 11, 'Hello'),
(6, 1, 1, 'Apple'),
(7, 1, 14, 'Deer'),
(8, 1, 14, 'Door'),
(9, 1, 15, 'Hello'),
(10, 1, 17, 'Hello'),
我想得到这样的报告：
first last count name
1 2 2 Apple
3 3 1 Bean
10 12 3 Hello
14 14 1 Deer
14 14 1 Door
15 15 1 Hello
17 17 1 Hello
换句话说，除了对连续的项目进行分组之外，当这些组的name值不同时，这些组还会拆分为单独的组。(换句话说，如果项目都是连续的并且具有完全相同的name，则它们仅在一个岛屿中(。我来过的最接近(而且不是很接近(是这样做的：
SELECT min(number) first_number, max(number) last_number, count(*) AS no_records FROM (
SELECT c.*, @rn := @rn + 1 rn
from (SELECT number FROM table WHERE cc = 1 GROUP BY number, name ORDER BY number) AS c
CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY number - rn, name ORDER BY number ASC
但是，这不起作用，并且发生的情况是，它似乎将名称的第一次出现返回为first，最后一次出现返回为last，no_records是它们之间的数字差异，这当然是不对的。
我觉得这个问题可能是相关的，但我无法理解它，当我尝试将其调整到我的表格时，它或多或少只是相当于一个简单的SELECT *。我需要对查询进行哪些修改才能使其正常工作？
请记住：
- 可以按任意顺序插入项目
- 数字可以重复
- 名称可以重复，不一定是连续的
您的查询中没有太多要更改的内容。您基本上需要在子查询中选择name和number，并按相同的顺序排序。然后，可以在外部查询中按name, number - rn分组。
SELECT
min(number) first_number,
max(number) last_number,
count(*) AS no_records,
name
FROM (
SELECT c.*, @rn := @rn + 1 rn
from (
SELECT name, number
FROM `table`
WHERE cc = 1
ORDER BY name, number
LIMIT 99999999999999999
) AS c
CROSS JOIN (SELECT @rn := 0) r
) c
GROUP BY name, number - rn
ORDER BY first_number ASC, name ASC;
结果：
first_number last_number no_records name
1 2 2 Apple
3 3 1 Bean
10 12 3 Hello
14 14 1 Deer
14 14 1 Door
15 15 1 Hello
17 17 1 Hello
数据库<>小提琴
我通常主张反对以这种方式使用会话变量。原因是此类解决方案依赖于内部实现，并且可能会因版本更新或设置更改而中断。例如：有一次MariaDB决定忽略没有LIMIT的子查询中的ORDER BY子句。这就是为什么我包含了一个巨大的限制。
我还在外部 ORDER BY 子句中用first_number替换了number，以避免ONLY_FULL_GROUP_BY模式出现问题。
生成行号的更稳定方法是在临时表中使用 AOTO_INCREMENT 列：
drop temporary table if exists tmp_tbl;
create temporary table tmp_tbl (
rn int unsigned auto_increment primary key,
name varchar(64) not null,
number int not null
);
insert into tmp_tbl (name, number)
select name, number
from `table`
order by name, number;
最终的 SELECT 查询与上面的外部查询相同：
SELECT
min(number) first_number,
max(number) last_number,
count(*) AS no_records,
name
FROM tmp_tbl
GROUP BY name, number - rn
ORDER BY first_number ASC, name ASC;
数据库<>小提琴
在较新的版本中(从MariaDB 10.2开始(，您可以使用ROW_NUMBER()窗口函数：
SELECT
min(number) first_number,
max(number) last_number,
count(*) AS no_records,
name
FROM (
SELECT
name,
number,
row_number() OVER (ORDER BY name, number) as rn
FROM `table`
WHERE cc = 1
) c
GROUP BY name, number - rn
ORDER BY first_number ASC, name ASC;
数据库<>小提琴
你的例子不是差距和孤岛问题。 如果它代表您的实际问题，则可以只使用聚合：
select min(number), max(number), count(*), name
from t
group by name;
我之所以这样说，是因为没有窗口函数，间隙和孤岛更具挑战性。 这就引出了一个问题，为什么你不使用更新版本的MariaDB。 无论如何，10.1 的生命周期结束是今年 10 月。
编辑：
作为一个缺口和岛屿，这有点棘手，因为每个名称都必须单独处理。 诀窍是将row_number()与分区一起使用：
select name, min(number), max(number), count(*)
from (select t.*,
row_number() over (partition by name order by number) as seqnum
from t
) t
group by name, (number - seqnum);
如果名称有相邻的数字并减去顺序值，则结果是常量。 例如：
Name Number Seq Diff
Hello 10 1 9
Hello 11 2 9
Hello 12 3 9
Hello 15 4 11
diff标识要聚合的组。
哎呀，我忘了这是针对即将过时的MariaDB版本：
select name, min(number), max(number), count(*)
from (select t.*,
(select count(*)
from `table` t2
where t2.name = t.name and t2.number <= t.number
) as seqnum
from `table` t
) t
group by name, (number - seqnum);
为了提高性能，您需要在(name, number)上有一个索引。 性能应该是合理的，除非名称超过几百行。
这是一个数据库<>小提琴。