两个UNION联合查询结果如何按原始StockInwardDate字段排序

核心逻辑是在子查询中额外返回同月份对应的原始日期值，外层直接用该字段排序即可，不会影响现有统计结果：

• 每个子查询的分组逻辑保持不变，新增MIN(StockInwardDate) AS raw_month_date字段，同月份的所有原始日期取任意值排序效果都一致，这里取最小值即可
• 外层分组新增raw_month_date字段，因为同月份的该字段值完全相同，不会改变原有分组逻辑
• 排序逻辑替换为按raw_month_date排序即可解决格式化字符串排序错乱的问题

额外优化建议：你原本使用的UNION会自动对两个子查询的结果去重，而外层本身已经做了聚合统计，替换为UNION ALL可以避免不必要的性能损耗，统计结果完全一致。

修改后完整SQL如下：

SELECT 
  month,
  sum(pcs) as pcs,
  sum(carat) as carat,
  sum(amount) as amount 
FROM (
  SELECT
    DATE_FORMAT(StockInwardDate, '%b-%Y') AS Month,
    MIN(StockInwardDate) AS raw_month_date,
    COUNT(*) as pcs,
    ROUND(SUM(Carat),2) as carat,
    ROUND(SUM(TotalAmount),2) as amount
  FROM tbl_sales
  GROUP BY DATE_FORMAT(StockInwardDate, '%m-%Y')

  UNION ALL

  SELECT
    DATE_FORMAT(StockInwardDate, '%b-%Y') AS Month,
    MIN(StockInwardDate) AS raw_month_date,
    COUNT(*) as pcs,
    ROUND(SUM(Carat),2) as carat,
    ROUND(SUM(TotalAmount),2) as amount
  FROM tbl_stock
  GROUP BY DATE_FORMAT(StockInwardDate, '%m-%Y') 
) AS tbl
GROUP BY tbl.Month, tbl.raw_month_date
ORDER BY tbl.raw_month_date DESC

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