SELECT a.electric_station_id, a.settlement_month, a.purchaser_name, COALESCE(SUM(a.cost_amount), 0) AS total_cost_amount, COALESCE(SUM(b.invoice_amount), 0) AS total_invoice_amount, COALESCE(SUM(c.payment_amount), 0) AS total_payment_amount FROM (SELECT electric_station_id, settlement_month, purchaser_name, SUM(cost_amount) AS cost_amount FROM cost_table GROUP BY electric_station_id, settlement_month, purchaser_name) a LEFT JOIN (SELECT electric_station_id, settlement_month, purchaser_name, SUM(invoice_amount) AS invoice_amount FROM invoice_table GROUP BY electric_station_id, settlement_month, purchaser_name) b ON a.electric_station_id = b.electric_station_id AND a.settlement_month = b.settlement_month AND a.purchaser_name = b.purchaser_name LEFT JOIN (SELECT electric_station_id, settlement_month, purchaser_name, SUM(payment_amount) AS payment_amount FROM payment_table GROUP BY electric_station_id, settlement_month, purchaser_name) c ON a.electric_station_id = c.electric_station_id AND a.settlement_month = c.settlement_month AND a.purchaser_name = c.purchaser_name GROUP BY a.electric_station_id, a.settlement_month, a.purchaser_name; 要求转换成jpa的Criteria api实现
