基于两列的连续区间查询：number连续且name相同的分组需求

Got it！我来帮你修改这个查询，解决同时按name和连续number分组的问题——毕竟原查询只考虑了number的连续性，没把name的一致性加进去，而且也没处理number重复的情况。

核心需求回顾 #

我们需要：

• 仅筛选cc=1的记录
• 只有当name完全相同，且number连续（包括重复值）时，才将数据归为同一连续区间
• 最终返回每个区间的起始number、结束number、记录数量及对应name

修改后的查询语句 #

因为你使用的是MariaDB 10.1（还不支持窗口函数），我们用用户自定义变量来实现分组逻辑：

SELECT
  name,
  MIN(number) AS first_number,
  MAX(number) AS last_number,
  COUNT(*) AS no_records
FROM (
  SELECT
    number,
    name,
    @rn := CASE
      -- 当name与上一行相同时
      WHEN @prev_name = name THEN
        CASE
          -- 同一name下number重复，保持分组标识不变
          WHEN number = @prev_num THEN @rn
          -- 同一name下number连续递增，分组标识+1
          WHEN number = @prev_num + 1 THEN @rn + 1
          -- 同一name下number不连续，重置分组标识
          ELSE 1
        END
      -- 当name与上一行不同时，重置分组标识
      ELSE 1
    END AS rn,
    -- 更新上一行的name和number变量
    @prev_name := name AS prev_name,
    @prev_num := number AS prev_num
  FROM (
    -- 先筛选cc=1的记录，按name和number排序（关键：确保同一name的记录集中，且number有序）
    SELECT number, name FROM `table` WHERE cc = 1 ORDER BY name, number
  ) AS sorted_records
  -- 初始化变量
  CROSS JOIN (SELECT @prev_name := NULL, @prev_num := NULL, @rn := 0) AS vars
) AS grouped_data
-- 按name和分组标识（number - rn）分组，确保同一name下的连续区间被归为一组
GROUP BY name, (number - rn)
-- 按name和起始number排序，让结果更清晰
ORDER BY name, first_number;

关键逻辑解释 #

1. 内层排序：必须先按name、再按number排序，这样同一name的记录会集中在一起，且number从小到大排列，这是变量正确计算的基础。
2. 变量分组逻辑：
   • @prev_name：记录上一行的name，用于判断当前行是否属于同一name组
   • @prev_num：记录上一行的number，用于判断当前number是否连续/重复
   • @rn：生成分组标识，只有当name相同且number连续/重复时，才保持或递增标识，否则重置
3. 最终分组：通过name和number - rn分组，同一name下的连续number会有相同的number - rn值，从而被归为同一区间。

测试数据的预期结果 #

针对你提供的测试数据，执行上述查询后会得到以下结果：

这个结果完全符合你的需求：同一name下的连续number被归为一组，重复number但不同name的被分开，非连续的number也被正确拆分为不同区间。

内容的提问来源于stack exchange，提问作者InterLinked