MySQL分组查询求助：按月统计各路线的数量总和

问题分析 #

你的需求是统计指定月份内各路线的quantity总和并按路线名称分组，当前查询存在几个问题：

• WHERE子句中的date未指定表别名，易引发字段歧义；
• 使用LEFT JOIN tbl_alloc后通过date过滤，会将无匹配分配记录的行直接过滤，效果等同于INNER JOIN；
• GROUP BY仅指定i.route_id，但SELECT中包含r.name，在严格SQL模式（如MySQL的ONLY_FULL_GROUP_BY）下会触发报错；
• 若当月某路线无数据，当前查询不会显示该路线，无法覆盖“全路线统计”的潜在需求。

修正后的查询语句 #

情况1：仅显示当月有数据的路线 #

SELECT 
    r.name, 
    SUM(i.quantity) AS target
FROM tbl_alloc_items i
INNER JOIN tbl_alloc a ON i.alloc_id = a.id
INNER JOIN tbl_routes r ON i.route_id = r.id
WHERE a.date BETWEEN '2022-10-01' AND '2022-10-31'
GROUP BY r.id, r.name;

情况2：显示所有路线（无数据的路线总和为0） #

如果需要包含当月没有任何分配记录的路线，调整连接顺序并将日期条件移至ON子句：

SELECT 
    r.name, 
    COALESCE(SUM(i.quantity), 0) AS target
FROM tbl_routes r
LEFT JOIN tbl_alloc_items i ON r.id = i.route_id
LEFT JOIN tbl_alloc a ON i.alloc_id = a.id 
    AND a.date BETWEEN '2022-10-01' AND '2022-10-31'
GROUP BY r.id, r.name;

关键说明 #

1. 字段明确性：给date字段加上表别名a.date，避免多表查询时的字段歧义；
2. JOIN逻辑调整：
   • 仅需有数据的路线时，用INNER JOIN过滤无关联记录的行；
   • 需全路线统计时，从tbl_routes出发做左连接，同时将日期条件放在ON子句中，避免过滤掉无匹配的路线；
3. GROUP BY规范：同时分组r.id和r.name，符合SQL标准，避免严格模式下的语法错误；
4. 空值处理：用COALESCE函数将无数据时SUM返回的NULL转换为0，保证结果格式统一。

内容的提问来源于stack exchange，提问作者Nasz Njoka Sr.