MySQL关联项目与付款表 保留明细行同时统计各项目总收款额

问题原因 #

你现有的查询使用了sum()聚合函数但没有指定GROUP BY分组规则，数据库会默认将关联后属于同一项目的所有行聚合为单行汇总结果，因此只会返回每个项目的一行数据，无法满足每条付款记录都携带项目总收款的需求。

解决方案 #

方案1：窗口函数实现（推荐，适配MySQL 8.0+、PostgreSQL、SQL Server等主流新版数据库）

使用分区聚合的窗口函数，不会合并原始行，会为每一行返回对应项目的总收款金额：

SELECT 
    pp.project_id,
    pp.id AS payment_id,
    pp.payment_amount,
    SUM(pp.payment_amount) OVER (PARTITION BY pp.project_id) AS total_received
FROM project_payments pp
-- 如果需要带出项目名称等字段，可以加上JOIN关联projects表
-- JOIN projects p ON p.id = pp.project_id
ORDER BY pp.project_id, pp.id

其中PARTITION BY pp.project_id表示按项目ID分组计算付款总额，每一行结果都会返回对应分组的聚合值，不会合并原始付款记录。

方案2：子查询关联实现（兼容不支持窗口函数的旧版数据库，比如MySQL 5.x）

先通过子查询统计每个项目的总收款，再和原始付款表关联映射到每一行：

SELECT 
    pp.project_id,
    pp.id AS payment_id,
    pp.payment_amount,
    pt.total_received
FROM project_payments pp
JOIN (
    SELECT project_id, SUM(payment_amount) AS total_received
    FROM project_payments
    GROUP BY project_id
) pt ON pp.project_id = pt.project_id
-- 如果需要带出项目名称等字段，可以加上JOIN关联projects表
-- JOIN projects p ON p.id = pp.project_id
ORDER BY pp.project_id, pp.id

两种方案执行后都可以得到你需要的查询结果。

内容的提问来源于stack exchange，提问作者Muntashir Are Rahi