如何实现分组内计数到5后停止，切换分组时重置计数器？

你的SQL逻辑存在变量执行顺序问题：SELECT @group_number := group_id会先将当前行的group_id赋值给@group_number，导致CASE语句里的group_id = @group_number永远为真。当第一个分组的@row_number累加到6后，后续所有行（包括新分组）都会触发@row_number := 6的分支，无法重置计数器。

方案1：使用窗口函数（MySQL 8.0+推荐） #

利用ROW_NUMBER()按分组生成行号，再通过LEAST()函数将超过5的行号替换为6，逻辑清晰且不易出错：

SELECT 
    group_id,
    LEAST(ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY (SELECT NULL)), 6) AS row_counter
FROM (
    SELECT 111 AS group_id UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 111 UNION ALL 
    SELECT 222 UNION ALL 
    SELECT 222 UNION ALL 
    SELECT 222 UNION ALL 
    SELECT 222 UNION ALL 
    SELECT 333 UNION ALL 
    SELECT 333
) m;

方案2：兼容低版本MySQL的变量写法 #

调整变量更新顺序，先判断分组是否变化，再更新行号，同时必须确保数据按group_id排序：

SELECT 
    group_id,
    row_counter
FROM (
    SELECT 
        group_id,
        CASE 
            WHEN group_id = @prev_group THEN 
                IF(@row < 6, @row := @row + 1, 6)
            ELSE 
                @row := 1
        END AS row_counter,
        @prev_group := group_id
    FROM (
        SELECT 111 AS group_id UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 111 UNION ALL 
        SELECT 222 UNION ALL 
        SELECT 222 UNION ALL 
        SELECT 222 UNION ALL 
        SELECT 222 UNION ALL 
        SELECT 333 UNION ALL 
        SELECT 333
    ) m
    CROSS JOIN (SELECT @prev_group := NULL, @row := 0) n
    ORDER BY group_id
) t;

两种方案执行后均会得到你期望的结果：每个分组前5行生成1-5的递增行号，超过5行的固定为6，切换分组时计数器自动重置。

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