MySQL按URI分组求平均分后分页出现重复数据的解决方法

问题出在排序规则的稳定性上：当多个uri分组的avg_score完全相同时，仅靠avg_score desc排序，数据库会对这些相同分数的分组随机排序。每次查询时这些分组的顺序可能变化，导致OFFSET偏移取到的记录和上一页重复。

解决方法很简单，在ORDER BY里追加一个唯一的排序字段，确保排序结果稳定。因为每个分组的uri是唯一的，用它作为第二排序条件最合适：

修改后的OFFSET 0查询SQL：

SELECT `uri`, avg(score) as avg_score 
FROM `feedback` 
WHERE `created_at` BETWEEN '2023-02-06 00:00:00' AND '2023-03-06 23:59:59' 
GROUP BY `uri` 
ORDER BY `avg_score` desc, `uri` asc 
LIMIT 8 OFFSET 0

OFFSET为8时的查询SQL同理：

SELECT `uri`, avg(score) as avg_score 
FROM `feedback` 
WHERE `created_at` BETWEEN '2023-02-06 00:00:00' AND '2023-03-06 23:59:59' 
GROUP BY `uri` 
ORDER BY `avg_score` desc, `uri` asc 
LIMIT 8 OFFSET 8

原理说明 #

当avg_score相同时，数据库会按照uri的字典序固定排序，每个分组的位置不再随机变化。不管执行多少次查询，相同条件下的排序结果完全一致，分页时就不会出现上一页的记录跑到下一页的情况。

如果需要，也可以用主键id作为第二排序条件（比如ORDER BY avg_score desc, id asc），只要保证排序的唯一性，就能解决分页重复的问题。

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