MySQL插入汇总行后UNION ALL查询无法按num_campaigns降序排序求助

问题根源 #

你的排序失效主要有两个原因：

1. 数据类型不匹配：插入的总和被存在category字段（大概率是字符串类型），后续查询中作为num_campaigns返回时是字符串格式，而第一个查询的num_campaigns是整数类型的计数。字符串排序会按字符逐个比较（比如'125'会被认为小于'30'，因为首字符'1' < '3'），导致排序结果不符合预期。
2. 冗余的GROUP BY：第二个查询已经通过WHERE campaign = 'Other'筛选出唯一一条记录，不需要额外的GROUP BY category，反而可能干扰结果。

修正后的查询语句 #

先统一num_campaigns的数值类型，同时改用UNION ALL避免不必要的去重操作：

SELECT category, COUNT(*) AS num_campaigns
FROM ccat
GROUP BY category
HAVING COUNT(*) >= 30
UNION ALL
SELECT campaign AS category, CAST(category AS UNSIGNED) AS num_campaigns
FROM ccat
WHERE campaign = 'Other'
ORDER BY num_campaigns DESC;

更优方案：无需修改原表 #

其实不需要提前向ccat插入'Other'记录，可以直接通过子查询动态生成汇总结果，既不污染原表数据，也能保证排序正确性：

-- 直接生成包含Other的汇总结果，无需插入记录
SELECT category, COUNT(*) AS num_campaigns
FROM ccat
GROUP BY category
HAVING COUNT(*) >= 30
UNION ALL
SELECT 'Other' AS category, SUM(num_campaigns) AS num_campaigns
FROM (
    -- 统计所有计数小于30的分类的数量
    SELECT COUNT(*) AS num_campaigns
    FROM ccat
    GROUP BY category
    HAVING COUNT(*) < 30
) AS sub_query
ORDER BY num_campaigns DESC;

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