MySQL多表查询：如何按月统计所有发票总金额？

我有两张表：

• 发票表files_fit_invoices，包含发票日期字段
• 发票明细表files_fit_invoices_items

我需要按月统计所有发票的总金额（即当月所有发票对应明细项的金额总和），但当前查询仅能统计每月单张发票的金额总和（可能是最后录入的那张）。

期望结果 #

当前错误结果 #

当前SQL语句 #

SELECT files_fit_invoices.file_inv_num, 
        CASE WHEN files_fit_invoices.file_inv_brk = 'add' 
                THEN (items.amount - ((items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100))) + (items.amount-(items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)))*(files_fit_invoices.file_inv_tax1/100)) 
             WHEN files_fit_invoices.file_inv_brk = 'brk' 
                THEN items.amount - (items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)) 
            ELSE items.amount-(items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)) 
        END AS H
FROM files_fit_invoices                          
INNER JOIN (
    SELECT files_fit_invoices_items.file_item_inv, SUM(files_fit_invoices_items.file_item_amount) AS amount 
    FROM files_fit_invoices_items 
    GROUP BY files_fit_invoices_items.file_item_inv
) items ON items.file_item_inv = files_fit_invoices.file_inv_num               
WHERE files_fit_invoices.file_inv_client = :client 
    AND DATE(files_fit_invoices.file_inv_date)>=STR_TO_DATE(:dateini,'%Y-%m-%d') 
    AND DATE(files_fit_invoices.file_inv_date)<=STR_TO_DATE(:datefin,'%Y-%m-%d') 
GROUP BY MONTH(files_fit_invoices.file_inv_date);

问题分析及修正方案 #

你的推测没错，问题核心在分组逻辑和汇总方式上：

1. 当前SELECT包含file_inv_num，但分组仅按月份，数据库会返回每个月份分组中某一条发票的金额，而非所有发票的总和
2. 没有对计算后的单张发票金额进行月度汇总

修正后的SQL语句 #

SELECT 
    DATE_FORMAT(files_fit_invoices.file_inv_date, '%b') AS month,
    CONCAT('$', SUM(
        CASE WHEN files_fit_invoices.file_inv_brk = 'add' 
            THEN (items.amount - ((items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100))) + (items.amount-(items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)))*(files_fit_invoices.file_inv_tax1/100)) 
        WHEN files_fit_invoices.file_inv_brk = 'brk' 
            THEN items.amount - (items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)) 
        ELSE items.amount-(items.amount*(files_fit_invoices.file_inv_comm/100)) + (items.amount*(files_fit_invoices.file_inv_disc/100)) 
        END
    )) AS total_amount
FROM files_fit_invoices                          
INNER JOIN (
    SELECT file_item_inv, SUM(file_item_amount) AS amount 
    FROM files_fit_invoices_items 
    GROUP BY file_item_inv
) items ON items.file_item_inv = files_fit_invoices.file_inv_num               
WHERE files_fit_invoices.file_inv_client = :client 
    AND DATE(files_fit_invoices.file_inv_date) >= STR_TO_DATE(:dateini,'%Y-%m-%d') 
    AND DATE(files_fit_invoices.file_inv_date) <= STR_TO_DATE(:datefin,'%Y-%m-%d') 
GROUP BY MONTH(files_fit_invoices.file_inv_date), DATE_FORMAT(files_fit_invoices.file_inv_date, '%b')
ORDER BY MONTH(files_fit_invoices.file_inv_date);

关键修正点 #

• 移除SELECT中的file_inv_num，避免分组后仅返回单条发票数据
• 用SUM()包裹CASE计算逻辑，对当月所有发票的最终金额进行汇总
• 用DATE_FORMAT将月份格式化为英文缩写（如Jan、Feb），同时按月份数字和缩写分组，确保分组准确性
• 添加ORDER BY按月份数字排序，保证结果按时间顺序展示

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