如何在MySQL 5.5中筛选超量的最早Technical_test_uuid？

需求回顾 #

要求限制用户可参与的去重后Technical_test_uuid数量不超过10，采用先进先出规则：

• 当用户提交的去重测试数量>10时，返回超出部分中最早提交的那些（比如13个时返回最早3个）
• 兼容测试数量未超10的场景（此时无返回结果）
  基于MySQL 5.5环境的tbluserquestion表，原SQL仅返回1条结果，不符合预期。

优化后的SQL实现 #

由于MySQL 5.5不支持窗口函数（如ROW_NUMBER()），我们使用用户变量来实现按用户分组的排序与计数：

SELECT user_id, technical_test_uuid, earliest_submit_time
FROM (
    SELECT 
        t.user_id,
        t.technical_test_uuid,
        t.earliest_submit_time,
        @row_num := IF(@prev_user = t.user_id, @row_num + 1, 1) AS row_rank,
        @prev_user := t.user_id
    FROM (
        -- 先获取每个用户每个测试的最早提交时间
        SELECT 
            user_id,
            technical_test_uuid,
            MIN(submit_time) AS earliest_submit_time
        FROM tbluserquestion
        GROUP BY user_id, technical_test_uuid
        ORDER BY user_id, earliest_submit_time ASC
    ) AS t
    CROSS JOIN (SELECT @prev_user := '', @row_num := 0) AS vars
) AS ranked
WHERE row_rank > 10;

逻辑说明 #

1. 内层子查询t：从tbluserquestion中按user_id和technical_test_uuid分组，取每个测试的最早提交时间，同时按用户和提交时间升序排序（确保最早的测试排在前面）。
2. 中间层排序计数：通过用户变量@prev_user判断是否为同一用户，@row_num为每个用户的测试按提交时间顺序编号（最早的为1，依次递增）。
3. 外层筛选：只保留编号大于10的记录，这些就是超出10个限制的最早提交测试；如果用户的测试数量≤10，这部分无结果，自然兼容未超限场景。

原SQL问题分析 #

原SQL仅返回1条结果，大概率是没有实现按用户分组的批量排序计数，比如只取了全局第一条超出记录，或者未正确处理分组逻辑。上述方案通过用户变量实现了每个用户独立的排序计数，能返回所有符合要求的超出项。

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