SQLite中按值范围分组并按sort_me字段排序结果的实现

需求说明 #

处理包含id（主键）、value（整数）、sort_me三列的表，需实现：

• 将value值相差≤50的行归为同一分组（传递性：若A与B符合、B与C符合，则A、B、C同组）
• 组内按sort_me升序排列ID
• 组间按各组的最小sort_me值升序排列
• 剔除仅含单个成员的分组

示例数据 #

id, value, sort_me
 1,     1,      15
 2,   102,      22
 3,     3,       7
 4,   101,      13
 5,   312,      53
 6,     6,      34
 7,   309,       3
 8,   104,       9
 9,   521,       8

预期结果 #

7,5
3,1,6
8,4,2

现有问题分析 #

当前查询语句：

SELECT concat_ws(',', v1.id, group_concat(v2.id ORDER BY v2.sort_me ASC))
    FROM t v1, t v2
    WHERE v1.id < v2.id AND abs(v1.value - v2.value) < 50
    GROUP BY v1.id
    ORDER BY v1.sort_me ASC

输出结果错误点：

• 生成了子分组（如3,6和1,3,6属于同一完整分组，却被拆分输出）
• 组内排序仅对v2生效，v1始终排在首位，导致组内顺序不符合sort_me升序要求
• 组间排序依赖v1.sort_me，而非分组的最小sort_me，导致整体顺序错误

正确查询语句 #

WITH recursive groups AS (
    -- 初始化：每行单独作为初始分组
    SELECT id, value, sort_me, id AS group_id
    FROM t
    UNION ALL
    -- 递归合并：将与分组内行value差≤50的未分组行并入同一分组
    SELECT t.id, t.value, t.sort_me, g.group_id
    FROM t
    JOIN groups g ON abs(t.value - g.value) <= 50 AND t.id NOT IN (SELECT id FROM groups)
),
-- 统一分组标识：取每个ID对应的最小group_id作为最终分组ID
group_ids AS (
    SELECT id, min(group_id) AS final_group_id
    FROM groups
    GROUP BY id
),
-- 分组聚合：收集ID并按sort_me排序，过滤单成员分组
grouped_data AS (
    SELECT 
        g.final_group_id,
        group_concat(t.id ORDER BY t.sort_me ASC) AS ids_list,
        min(t.sort_me) AS min_sort
    FROM group_ids g
    JOIN t ON g.id = t.id
    GROUP BY g.final_group_id
    HAVING count(*) > 1
)
-- 按分组最小sort_me升序输出
SELECT ids_list
FROM grouped_data
ORDER BY min_sort ASC;

逻辑说明 #

1. 递归CTE（groups）：通过递归找出所有value连通的行（传递性分组），确保符合条件的行被归为同一组
2. 分组标识统一（group_ids）：解决递归过程中同一行可能被分配多个group_id的问题，用最小group_id作为分组唯一标识
3. 分组聚合（grouped_data）：按最终分组ID聚合，收集ID并按sort_me排序，同时过滤掉成员数≤1的分组
4. 组间排序：基于分组的最小sort_me值排序，保证组间顺序符合要求

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