MySQL中使用Group By结合Case实现特定类别行分组的排序与分组结果异常问题

我明白你的需求：只对prod_cat='200'（Shipping类别）的行按item_no分组，其他类别的行要保留所有原始记录，同时还要保证所有行能按照指定的ORDER BY规则排序。你之前遇到的问题确实是GROUP BY里的ELSE分支导致的，我来给你拆解一下原因，再提供可行的解决方案。

问题根源分析 #

• 用ELSE RAND()的问题：当你在GROUP BY里用RAND()作为非Shipping类别的分组键时，每一行都会生成一个随机值，相当于每行单独成组。但GROUP BY的执行顺序在ORDER BY之前，随机的分组键会打乱后续排序的逻辑，导致最终结果无法按照你指定的prod_cat、user_def_fld_3和id顺序排列。
• 去掉ELSE的问题：此时非Shipping类别的行在CASE WHEN里会返回NULL，GROUP BY会把所有NULL的行归为同一组，所以这部分只会返回一行聚合结果，而不是你需要的全量行。

解决方案：用UNION ALL拆分处理 #

最直观的解决方式是把查询拆成两部分，分别处理Shipping类别和其他类别，再用UNION ALL合并结果，最后统一排序：

-- 处理Shipping类别（prod_cat='200'）：按item_no分组
SELECT imcatfil_sql.prod_cat, imitmidx_sql.item_no 
FROM estimate 
JOIN imitmidx_sql ON estimate.item_no=imitmidx_sql.item_no 
JOIN imcatfil_sql ON imitmidx_sql.prod_cat=imcatfil_sql.prod_cat 
WHERE budget_header_id=19303 
  AND (deleted_by_id=0 OR deleted_by_id IS NULL) 
  AND estimate.item_no NOT IN('157','156','158') 
  AND hidden=0 
  AND item_desc_1 NOT LIKE '%SALES TAX%'
  AND imitmidx_sql.prod_cat='200'
GROUP BY imcatfil_sql.prod_cat, imitmidx_sql.item_no

UNION ALL

-- 处理其他类别：不分组，保留所有符合条件的行
SELECT imcatfil_sql.prod_cat, imitmidx_sql.item_no 
FROM estimate 
JOIN imitmidx_sql ON estimate.item_no=imitmidx_sql.item_no 
JOIN imcatfil_sql ON imitmidx_sql.prod_cat=imcatfil_sql.prod_cat 
WHERE budget_header_id=19303 
  AND (deleted_by_id=0 OR deleted_by_id IS NULL) 
  AND estimate.item_no NOT IN('157','156','158') 
  AND hidden=0 
  AND item_desc_1 NOT LIKE '%SALES TAX%'
  AND imitmidx_sql.prod_cat!='200'

-- 统一排序，保证结果顺序符合要求
ORDER BY prod_cat, user_def_fld_3, estimate.id;

方案说明 #

• 拆分逻辑：第一部分只筛选prod_cat='200'的行，按prod_cat和item_no分组（很多SQL模式要求GROUP BY包含所有非聚合列，所以加上prod_cat更稳妥）；第二部分筛选非200的行，不做分组，直接返回所有符合条件的记录。
• UNION ALL的优势：不会去重，能完整保留两部分的结果，而且性能比UNION更好（不需要做去重校验）。
• 统一排序：把ORDER BY放在整个UNION ALL查询的最后，确保所有行都按照你需要的规则排序。

可选优化：用窗口函数标记分组（适用于支持窗口函数的数据库） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），也可以用ROW_NUMBER()来标记Shipping类别中每个item_no的第一行，然后筛选出分组后的行和其他所有行：

WITH cte AS (
  SELECT 
    imcatfil_sql.prod_cat, 
    imitmidx_sql.item_no,
    imitmidx_sql.user_def_fld_3,
    estimate.id,
    -- 对Shipping类别，按item_no分组后标记第一行；其他类别按id单独分组，保留所有行
    ROW_NUMBER() OVER (PARTITION BY CASE WHEN imitmidx_sql.prod_cat='200' THEN imitmidx_sql.item_no ELSE estimate.id END ORDER BY estimate.id) AS rn
  FROM estimate 
  JOIN imitmidx_sql ON estimate.item_no=imitmidx_sql.item_no 
  JOIN imcatfil_sql ON imitmidx_sql.prod_cat=imcatfil_sql.prod_cat 
  WHERE budget_header_id=19303 
    AND (deleted_by_id=0 OR deleted_by_id IS NULL) 
    AND estimate.item_no NOT IN('157','156','158') 
    AND hidden=0 
    AND item_desc_1 NOT LIKE '%SALES TAX%'
)
SELECT prod_cat, item_no
FROM cte
WHERE rn=1
ORDER BY prod_cat, user_def_fld_3, id;

这个方案的逻辑是：对Shipping类别，按item_no分区，每个分区只保留第一行；对其他类别，按estimate.id分区（每行单独分区），所以所有行都会保留rn=1，最终结果既实现了Shipping类别的分组，又保留了其他类别的全量行，同时排序正常。

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