MySQL5.7按客户分组查询当月及上月营业收入的脚本需求

实现思路 #

因为MySQL 5.7版本不支持窗口函数、CTE语法，所以采用自关联的方式实现上月营收匹配：

• 先按照原有逻辑统计每个客户每个月的营业收入，生成基础月度统计结果集
• 对基础结果集做自关联，关联规则为客户ID完全匹配，且上月统计的月份 = 当月统计的月份减1个月
• 关联后直接取上月结果集的营收值即可，无上月数据可按需返回0或者空值

可直接运行的SQL代码 #

SELECT 
    curr_month.`DATE`,
    curr_month.ACCOUNTID,
    curr_month.`CLIENT NAME`,
    curr_month.REVENUE,
    -- 无上月营收时默认返回0，如需返回NULL可去掉IFNULL包装
    IFNULL(prev_month.REVENUE, 0) AS PMREVENUE
FROM (
    -- 当月营收统计逻辑，和你原有写法保持一致
    SELECT 
        LAST_DAY(i.`Date`) AS `DATE`,
        c.Id AS ACCOUNTID,
        c.`CLIENT NAME`,
        SUM(i.Subtotal) AS REVENUE
    FROM Invoice i
    INNER JOIN Clients c ON c.Id = i.ClientId 
    GROUP BY LAST_DAY(i.`Date`), c.Id, c.`CLIENT NAME`
) AS curr_month
-- 左关联匹配上月同客户的营收数据
LEFT JOIN (
    SELECT 
        LAST_DAY(i.`Date`) AS `DATE`,
        c.Id AS ACCOUNTID,
        SUM(i.Subtotal) AS REVENUE
    FROM Invoice i
    INNER JOIN Clients c ON c.Id = i.ClientId
    GROUP BY LAST_DAY(i.`Date`), c.Id
) AS prev_month 
ON 
    curr_month.ACCOUNTID = prev_month.ACCOUNTID 
    AND prev_month.`DATE` = DATE_SUB(curr_month.`DATE`, INTERVAL 1 MONTH)
ORDER BY curr_month.`DATE` DESC;

调整说明 #

• 原SQL的关联条件Clients.Id = Invoice.Id存在逻辑问题，正常发票表会存储关联的客户ID字段（常见字段名如ClientId），已在代码中修正，你可以根据实际表结构的字段名调整关联规则
• 所有语法均兼容MySQL 5.7.22版本，未使用LAG、WITH等受限语法

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