MySQL中如何基于日期分组合并UNION查询的多行列值？

你的问题出在GROUP BY的字段选择上——原查询把所有业务相关字段都加入了分组条件，导致同日期但不同业务类型的记录无法合并到一行。要实现同日期数据合并，需要仅按date分组，同时对每个业务字段使用聚合函数来合并值。

修改后的查询语句 #

SELECT
    date,
    GROUP_CONCAT(DISTINCT raw_material_bill_no SEPARATOR ', ') AS raw_material_bill_no,
    SUM(IF(raw_material_payment = '', 0, raw_material_payment)) AS raw_material_payment,
    GROUP_CONCAT(DISTINCT billings_no SEPARATOR ', ') AS billings_no,
    SUM(IF(billing_payment = '', 0, billing_payment)) AS billing_payment,
    GROUP_CONCAT(DISTINCT expense_no SEPARATOR ', ') AS expense_no,
    SUM(IF(expense_payment = '', 0, expense_payment)) AS expense_payment,
    GROUP_CONCAT(DISTINCT old_payment_no SEPARATOR ', ') AS old_payment_no,
    SUM(IF(old_payment = '', 0, old_payment)) AS old_payment
FROM
(
  SELECT
    raw_material_payment.paying_date AS date,
    raw_materials.bill_no AS raw_material_bill_no ,
    raw_material_payment.paying_amount + raw_material_payment.less AS raw_material_payment ,
    "" AS billings_no ,
    "" AS billing_payment ,
    "" AS expense_no ,
    "" AS expense_payment ,
    "" AS old_payment_no ,
    "" AS old_payment
  from raw_materials
  INNER JOIN raw_material_payment on raw_material_payment.raw_material_id = raw_materials.id
  UNION ALL
  SELECT
    billings.date AS date ,
    "" AS raw_material_bill_no ,
    "" AS raw_material_payment ,
    billings.id AS billings_no ,
    billing_payments.paying_amount + billing_payments.less AS billing_payment ,
    "" AS expense_no ,
    "" AS expense_payment ,
    "" AS old_payment_no ,
    "" AS old_payment
  FROM billings
  INNER JOIN billing_payments on billing_payments.billing_id=billings.id
  UNION ALL
  SELECT
    expense.bill_date AS date ,
    "" AS raw_material_bill_no ,
    "" AS raw_material_payment ,
    "" AS billings_no ,
    "" AS billing_payment ,
    expense.bill_no AS expense_no ,
    "" AS expense_payment ,
    "" AS old_payment_no ,
    "" AS old_payment
  FROM expense
  UNION ALL
  SELECT
    old_payments.bill_date AS date ,
    "" AS raw_material_bill_no ,
    "" AS raw_material_payment ,
    "" AS billings_no ,
    "" AS billing_payment ,
    "" AS expense_no ,
    "" AS expense_payment ,
    old_payments.bill_no AS old_payment_no ,
    old_payments.total AS old_payment
  FROM old_payments
) as report
GROUP BY date;

关键说明 #

1. 字符串类型字段处理：用GROUP_CONCAT(DISTINCT ...)合并同日期下的多个单号，DISTINCT避免重复值，SEPARATOR ', '用逗号分隔多个单号。
2. 金额字段处理：用SUM(IF(字段='', 0, 字段))把空字符串转换为0后求和，确保金额计算准确。
3. 分组条件：仅保留date作为分组字段，确保同日期的所有业务数据都合并到一行。

内容的提问来源于stack exchange，提问作者B Thiru Yogeshwaran