SQL JOIN后按多字段分组：按姓名与完成月份汇总任务成本

核心修改思路 #

基于你现有的关联查询，我们需要通过聚合函数计算成本总和，同时按「姓名」和「任务完成月份」分组，关键是要正确处理日期格式转换与分组字段的匹配。

针对不同数据库的查询语句 #

MySQL 版本

SELECT 
  users.name AS 姓名,
  SUM(COALESCE(tasks.cost, 0)) AS 成本总和,
  MONTHNAME(tasks.completed) AS 月份
FROM users
JOIN tasks ON tasks.assignee = users.id
WHERE tasks.completed IS NOT NULL AND tasks.assignee IS NOT NULL
GROUP BY users.name, MONTH(tasks.completed), MONTHNAME(tasks.completed)
ORDER BY MONTH(tasks.completed), users.name;

PostgreSQL 版本

SELECT 
  users.name AS 姓名,
  SUM(COALESCE(tasks.cost, 0)) AS 成本总和,
  TO_CHAR(tasks.completed, 'FMMonth') AS 月份
FROM users
JOIN tasks ON tasks.assignee = users.id
WHERE tasks.completed IS NOT NULL AND tasks.assignee IS NOT NULL
GROUP BY users.name, EXTRACT(MONTH FROM tasks.completed), TO_CHAR(tasks.completed, 'FMMonth')
ORDER BY EXTRACT(MONTH FROM tasks.completed), users.name;

SQL Server 版本

SELECT 
  users.name AS 姓名,
  SUM(ISNULL(tasks.cost, 0)) AS 成本总和,
  DATENAME(MONTH, tasks.completed) AS 月份
FROM users
JOIN tasks ON tasks.assignee = users.id
WHERE tasks.completed IS NOT NULL AND tasks.assignee IS NOT NULL
GROUP BY users.name, DATEPART(MONTH, tasks.completed), DATENAME(MONTH, tasks.completed)
ORDER BY DATEPART(MONTH, tasks.completed), users.name;

关键细节说明 #

1. 聚合求和：用SUM()计算分组内的成本总和，保留COALESCE(tasks.cost, 0)（SQL Server用ISNULL）确保空成本值被当作0计算，避免求和结果失真。
2. 月份转换：通过数据库内置函数将日期字段转换为英文月份名称，保证输出格式符合你的预期。
3. 分组规则：GROUP BY必须包含所有SELECT中的非聚合字段（姓名、月份相关字段），同时加入月份数字（如MONTH(tasks.completed)）是为了排序时能按自然月份顺序排列，避免出现字母排序导致的顺序混乱（比如February排在January前面）。
4. 排序优化：最后添加ORDER BY让结果按月份、姓名有序输出，更易读。

预期输出结果 #

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