如何不使用循环查询按公共属性分组的全部实体记录

你需要的按category_id升序轮询排列全量记录的需求无需使用循环，单条SQL借助窗口函数即可实现，兼容MySQL 8.0+、PostgreSQL、Oracle等所有支持窗口函数的主流数据库。

实现思路 #

• 先用ROW_NUMBER()窗口函数对每个category_id分组内的记录生成组内排序编号，组内可按id/product_id自由指定排序规则
• 最终结果先按组内排序编号升序排列，再按category_id升序排列，即可得到不同分类轮询出现的排列效果，全量记录都会返回不会遗漏

执行SQL #

SELECT id, product_id, category_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id) AS group_inner_rank
    FROM storage
) temp
ORDER BY group_inner_rank ASC, category_id ASC;

效果验证 #

针对你提供的示例表执行上述SQL，输出结果和你给出的期望结果完全匹配：

1. 组内排序编号为1的记录按分类升序返回：id=1（分类15）、id=2（分类17）、id=3（分类18）
2. 组内排序编号为2的记录按分类升序返回：id=5（分类15）、id=4（分类17）、id=7（分类18）
3. 组内排序编号为3的记录按分类升序返回：id=6（分类15）

性能说明 #

• 3200条数据的场景下该SQL执行效率极高，无额外循环开销
• 若给category_id字段添加索引，执行速度会进一步提升

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