MySQL：按3列分组统计指定日期范围内count列总和

核心实现SQL语句 #

SELECT
    ROW_NUMBER() OVER (ORDER BY mcc, partner, result) AS id,
    CONCAT('2022-07-13', ' - ', '2022-07-14') AS date,
    SUM(count) AS count,
    mcc,
    partner,
    result
FROM Stats
WHERE date BETWEEN '2022-07-13' AND '2022-07-14'
GROUP BY mcc, partner, result
ORDER BY mcc, partner, result;

关键逻辑说明 #

• 求和与分组：用SUM(count)计算每组的count总和，通过GROUP BY mcc, partner, result按指定三列聚合数据；
• 日期范围筛选：通过WHERE date BETWEEN '起始日期' AND '结束日期'过滤目标时间范围内的记录；
• 日期格式拼接：利用CONCAT()函数将起止日期拼接成YYYY-MM-DD - YYYY-MM-DD的格式；
• 自增id生成：通过ROW_NUMBER() OVER (ORDER BY ...)生成符合期望的自增序列id；
• 结果排序：最后用ORDER BY保证输出顺序与示例一致。

灵活化改造（参数化日期范围） #

如果需要频繁调整日期范围，可通过变量实现参数化，避免硬编码：

SET @start_date = '2022-07-13';
SET @end_date = '2022-07-14';

SELECT
    ROW_NUMBER() OVER (ORDER BY mcc, partner, result) AS id,
    CONCAT(@start_date, ' - ', @end_date) AS date,
    SUM(count) AS count,
    mcc,
    partner,
    result
FROM Stats
WHERE date BETWEEN @start_date AND @end_date
GROUP BY mcc, partner, result
ORDER BY mcc, partner, result;

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