MariaDB按分类统计合同月度金额的条件SUM查询实现

基础环境与表结构 #

开发环境为MariaDB + phpMyAdmin，核心业务表CONTRACTS用于存储合同信息，支持标记两种计费周期：YEARLY（年付）、MONTHLY（月付），表结构及存储样例如下：

现存问题 #

原有查询直接对合同售价求和，未做周期换算，会把年付合同的全年总金额直接计入月度统计，导致结果偏差。原语句还存在表名拼写错误（contratos/categorias为拼写失误，对应应为contracts/categories），原语句如下：

SELECT SUM(contracts.salesprice), `categories`.*
        FROM `contracts` 
        LEFT JOIN `categories` ON `contratos`.`cat_id` = `categories`.`id_cat`
     GROUP BY categorias.descripcion_cat;

统计换算规则：

• 年付合同（CICLE = 'YEARLY'）：售价除以12折算为月度等效金额后参与求和
• 月付合同（CICLE = 'MONTHLY'）：直接取售价原值参与求和

修正后SQL语句 #

使用CASE WHEN条件分支逐行计算单条合同的月度金额，再传入SUM聚合函数分组统计即可，语句如下：

SELECT 
    SUM(
        CASE
            WHEN contracts.CICLE = 'YEARLY' THEN contracts.salesprice / 12
            WHEN contracts.CICLE = 'MONTHLY' THEN contracts.salesprice
            ELSE 0
        END
    ) AS monthly_total,
    `categories`.*
FROM `contracts`
LEFT JOIN `categories` ON `contracts`.`cat_id` = `categories`.`id_cat`
GROUP BY categories.id_cat, categories.descripcion_cat;

写法说明 #

• CASE WHEN是SQL标准的条件判断语法，逐行匹配字段值返回对应计算结果，逻辑和通用编程语言的if/else分支完全一致
• 聚合函数SUM()会对CASE WHEN返回的每行月度折算金额做累加，最终得到分组后的月度总金额
• 语句中把分类表主键id_cat加入GROUP BY子句，是为了兼容MariaDB默认开启的ONLY_FULL_GROUP_BY语法校验，避免非聚合字段未参与分组导致的报错
• ELSE 0分支用于兜底处理周期字段为空、值非法的脏数据，避免异常值导致统计结果失真

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