MySQL如何为每个分类生成1-12月序列并按分类优先排序输出

解决方案 #

你可以通过调整笛卡尔积的关联顺序+增加显式排序规则实现需求，修改后的SQL如下：

SELECT mon, @c:=@c+1 as cat_no, category
FROM
-- 分类表放在关联顺序的前面，保证优先按分类遍历
(SELECT 'DEBIT' as category, 1 AS cate_sort UNION SELECT 'CREDIT' as category, 2 AS cate_sort) b,
(
 SELECT @m:=@m+1 as mon FROM
(
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION 
  SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) a,
(SELECT @m:=0) c
) d,
(SELECT @c:=0) e
-- 显式指定排序规则，结果更稳定
ORDER BY cate_sort ASC, mon ASC;

调整说明

• 给分类表新增了固定排序字段cate_sort，不需要依赖分类名称的字母序即可保证DEBIT排在CREDIT前面，后续新增分类也可以直接调整排序字段值实现自定义顺序
• 分类表放在关联逻辑的最前方，笛卡尔积会优先遍历每一个分类，再关联所有月份，自然就会先输出单个分类的全部12个月数据，再切换到下一个分类
• 新增显式ORDER BY规则，避免不同MySQL版本的默认遍历逻辑差异导致输出顺序不稳定

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