MySQL如何合并stok_in和stok_out表按月份分组汇总出入库数据

错误原因说明 #

你原有SQL使用UNION纵向合并结果集，会将入库统计和出库统计拆分为独立行，无法在同一行展示对应月份的出入库汇总值。

正确实现SQL #

SELECT 
  IFNULL(out_stat.sum_jml_out, 0) AS `Sum(jml_out)`,
  all_months.month_name AS `Monthname(tggl_out)`,
  IFNULL(in_stat.sum_jml_in, 0) AS `Sum(jml_in)`,
  all_months.month_name AS `Monthname(tggl_in)`
FROM (
  -- 去重获取两张表所有涉及的月份，避免遗漏仅入库/仅出库的月份
  SELECT monthname(tggl_out) AS month_name FROM stok_out
  UNION
  SELECT monthname(tggl_in) AS month_name FROM stok_in
) AS all_months
-- 关联按月统计的出库数据
LEFT JOIN (
  SELECT 
    monthname(tggl_out) AS out_month,
    SUM(jml_out) AS sum_jml_out
  FROM stok_out
  GROUP BY out_month
) AS out_stat ON all_months.month_name = out_stat.out_month
-- 关联按月统计的入库数据
LEFT JOIN (
  SELECT 
    monthname(tggl_in) AS in_month,
    SUM(jml_in) AS sum_jml_in
  FROM stok_in
  GROUP BY in_month
) AS in_stat ON all_months.month_name = in_stat.in_month

逻辑说明 #

• 先通过去重合并两张表的所有月份作为主表，确保不会丢失只有入库或者只有出库的月份数据
• 分别关联出库、入库的按月统计结果，将同一月份的两个汇总值匹配到同一行
• 用IFNULL处理某个月没有入库/出库的场景，空值默认返回0，符合统计场景的常规要求

可选优化（按自然月份排序） #

如果需要按1-12月的自然时间排序，而非月份名称的字母顺序，可以在SQL末尾添加排序逻辑：

ORDER BY MONTH(STR_TO_DATE(CONCAT('1 ', all_months.month_name, ' 2024'), '%d %M %Y'))

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