MySQL中如何从关联表计算指定用户的得分？

问题背景 #

我有两张表，数据如下：
Scores 表：

postId score
1      1
2      -1
1      1
2      2

Posts 表：

id user
1  me
2  you
3  me
3  0

我需要编写一个名为getScoreForUser(user)的存储过程，用来计算指定用户的总得分，预期调用结果如下：

• CALL getScoreForUser('me') 应该返回 2（用户'me'的post1有两个得分1，总和为2；post3没有对应的score记录，不影响）
• CALL getScoreForUser('you') 应该返回 1（用户'you'的post2得分是-1+2=1）

我之前尝试了错误的关联写法，比如select score.scores FROM scores INNER JOIN posts ON (scores.postId=posts.i...，没得到正确结果，希望能得到正确的存储过程写法。

正确的存储过程实现 #

这是符合需求的存储过程代码，我给你拆解下逻辑：

DELIMITER //

CREATE PROCEDURE getScoreForUser(IN target_user VARCHAR(255))
BEGIN
    -- 计算指定用户所有帖子的得分总和
    SELECT COALESCE(SUM(s.score), 0) AS total_score
    FROM Posts p
    LEFT JOIN Scores s ON p.id = s.postId
    WHERE p.user = target_user
    GROUP BY p.user;
END //

DELIMITER ;

逻辑说明：

1. 分隔符调整：因为存储过程内部会用到分号，所以先临时把语句分隔符改成//，避免和存储过程内的分号冲突，最后再改回默认的;。
2. 参数接收：IN target_user VARCHAR(255)用来接收调用时传入的用户名参数，你可以根据实际表字段的长度调整VARCHAR的数值。
3. 关联与求和：
   • 用LEFT JOIN确保即使用户的帖子没有对应的score记录（比如用户'me'的post3），也会被纳入查询，不会漏掉用户的任何帖子。
   • COALESCE(SUM(s.score), 0)用来处理用户没有任何得分记录的极端情况，此时会返回0而不是NULL，更贴合实际业务需求。
   • 通过WHERE p.user = target_user筛选出指定用户的所有帖子，再用SUM(s.score)计算该用户的总得分。

测试验证 #

调用存储过程测试效果：

CALL getScoreForUser('me'); -- 返回2
CALL getScoreForUser('you'); -- 返回1
CALL getScoreForUser('someone'); -- 返回0（无对应数据时的默认值）

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