MariaDB 5.5下单查询实现每个分类取24条门店数据并支持分页

需求说明 #

需要通过单次查询实现按分类截取关联门店的效果，避免服务端执行多次查询：

• 针对每个分类查询关联的门店，每个分类最多返回24条数据，若有5个分类则总结果最多120条
• 支持通过LIMIT、OFFSET实现全局分页能力
  

原有问题分析 #

最初编写的SQL直接对子查询加LIMIT，会全局截取前24条关联关系，无法按分类分别截取：

SELECT *
FROM categories c
    LEFT JOIN (SELECT * FROM stores_categories LIMIT 24 OFFSET 0) sc ON sc.id_category = c.id
WHERE type = 'STORE' OR type = 'ALL';

同类场景的现有实现仅支持每个分类取固定条数，不支持自定义分页：

SELECT sc1.*
FROM stores_categories sc1
    LEFT OUTER JOIN stores_categories sc2 ON (sc1.id_category = sc2.id_category AND sc1.id_store < sc2.id_store)
GROUP BY sc1.id_store
HAVING COUNT(*) < 24
ORDER BY sc1.id_category;

适配MariaDB 5.5的最终方案 #

MariaDB 5.5不支持窗口函数，使用用户变量实现分类行号标记，兼容版本要求同时支持分页：

SELECT * FROM (
    SELECT 
        c.*,
        sc.*,
        -- 同分类内行号累加，分类切换时行号重置为1
        @row_num := IF(@curr_cate = sc.id_category, @row_num + 1, 1) AS cate_row_num,
        @curr_cate := sc.id_category
    FROM categories c
    LEFT JOIN stores_categories sc ON sc.id_category = c.id
    WHERE c.type IN ('STORE', 'ALL')
    -- 可调整排序规则，比如按门店创建时间倒序替换为 ORDER BY c.id, sc.create_time DESC
    ORDER BY c.id, sc.id_store
) AS temp_result, (SELECT @row_num := 0, @curr_cate := 0) AS var_init
-- 每个分类最多返回24条
WHERE temp_result.cate_row_num <= 24
-- 分页参数，按业务需求调整，比如单页60条则写 LIMIT 60 OFFSET 0
LIMIT 120 OFFSET 0;

方案说明 #

• 内层查询先对所有关联结果按分类排序，通过用户变量给每个分类下的门店生成独立行号
• 外层筛选行号≤24的记录，保证每个分类返回的门店数不超过上限
• 最外层的LIMIT和OFFSET可直接用于全局分页，不需要额外调整逻辑
• 调整内层ORDER BY的字段即可自定义每个分类下门店的排序规则

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