MySQL如何实现按类型比例混合返回结果并支持分页查询

按Type比例混合返回分页数据的实现方案 #

MySQL 8.0及以上版本方案（推荐，灵活支持动态比例配置） #

利用窗口函数给每个Type下的行单独排序编号，再按你需要的比例取对应条数即可，新增Type仅需调整配置规则，无需修改核心逻辑。

基础实现（返回2条type=1 + 1条type=2）

WITH type_ranked AS (
    SELECT 
        *,
        -- 按Type分组，每组内按ID升序排序编号
        ROW_NUMBER() OVER(PARTITION BY Type ORDER BY ID) AS group_rn
    FROM 你的表名
)
SELECT ID, Name, Type
FROM type_ranked
WHERE 
    (Type = 1 AND group_rn <= 2)
    OR (Type = 2 AND group_rn <= 1)
ORDER BY ID
LIMIT 3;

多Type比例配置适配

如果后续需要LIMIT 10，按Type1到Type5的比例(2,3,2,1,2)返回，仅需调整WHERE条件的阈值即可：

WITH type_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Type ORDER BY ID) AS group_rn
    FROM 你的表名
)
SELECT ID, Name, Type
FROM type_ranked
WHERE 
    (Type = 1 AND group_rn <= 2)
    OR (Type = 2 AND group_rn <= 3)
    OR (Type = 3 AND group_rn <= 2)
    OR (Type = 4 AND group_rn <= 1)
    OR (Type = 5 AND group_rn <= 2)
ORDER BY ID
LIMIT 10;

分页翻页实现

翻页时只需记录上一页各Type的最大group_rn值，下一次查询增加大于该值的过滤条件即可。比如第二页查询2条type=1+1条type=2的写法：

WITH type_ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Type ORDER BY ID) AS group_rn
    FROM 你的表名
)
SELECT ID, Name, Type
FROM type_ranked
WHERE 
    (Type = 1 AND group_rn > 2 AND group_rn <= 4)
    OR (Type = 2 AND group_rn > 1 AND group_rn <= 2)
ORDER BY ID
LIMIT 3;

MySQL 5.x兼容方案 #

如果使用不支持窗口函数的低版本MySQL，可以用变量实现分组编号：

SELECT ID, Name, Type
FROM (
    SELECT 
        t.*,
        @rn := IF(@curr_type = Type, @rn + 1, 1) AS group_rn,
        @curr_type := Type
    FROM 你的表名 t
    JOIN (SELECT @curr_type := 0, @rn := 0) AS init_vars
    ORDER BY Type, ID
) AS ranked_data
WHERE 
    (Type = 1 AND group_rn <= 2)
    OR (Type = 2 AND group_rn <= 1)
ORDER BY ID
LIMIT 3;

动态配置优化建议 #

可以单独建一张Type比例配置表存储规则，比如表type_page_config字段为Type、page_limit，后续调整比例、新增Type仅需修改该表数据，无需修改SQL，查询时关联配置表过滤group_rn <= page_limit即可。

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