如何用MySQL单查询将排序后的数据分为指定批次并分组排列？

核心解决方案（MySQL 8.0+） #

假设目标表名为test_table，需要按value降序排序后分成3个均等批次，可使用以下查询：

SELECT id, value
FROM (
    SELECT 
        id, 
        value,
        ((ROW_NUMBER() OVER (ORDER BY value DESC) - 1) % 3) + 1 AS helper
    FROM test_table
) t
ORDER BY helper, value DESC;

逻辑拆解 #

1. 生成排序行号：子查询中通过ROW_NUMBER() OVER (ORDER BY value DESC)生成按value降序排列的连续行号（从1开始）。
2. 计算批次分组标识：利用((行号 - 1) % 批次数量) + 1计算helper列，实现行号1、4、7...归为批次1，行号2、5、8...归为批次2，行号3、6、9...归为批次3的分组逻辑，完全匹配需求中的辅助列规则。
3. 按批次排序：外层查询通过ORDER BY helper, value DESC先按批次分组，组内保持value降序排列，得到期望的结果。

MySQL 5.x兼容方案 #

如果使用MySQL 5.x版本（不支持窗口函数），可通过用户变量模拟行号：

SELECT id, value
FROM (
    SELECT 
        id, 
        value,
        ((@row_num := @row_num + 1) - 1) % 3 + 1 AS helper
    FROM test_table, (SELECT @row_num := 0) init
    ORDER BY value DESC
) t
ORDER BY helper, value DESC;

扩展性说明 #

该方案支持任意批次数量：只需将查询中的3替换为目标批次数量N即可。例如要分成4个批次，只需把% 3和+ 1前的3改为4。

示例验证 #

针对题目中的示例数据，执行上述查询后将得到如下结果（空白行仅用于区分批次，实际查询结果无空白行）：

id     value
8      900
1      356
6      222
5      654
3      321
4      123
9      489
2      243
7      102

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