使用一对多关系的SQL求和

Question

我有两张表格

comments
------------------
id       text

rates
---------------------
id    comment_id    score

我的问题是...但sum操作不受影响，只返回一行。

SELECT *, SUM(rates.score) FROM comment LEFT JOIN rate ON (rate.comment_id = comment.id)

- sweb

Answer 1

SELECT comment.*, SUM(rates.score) FROM comment LEFT JOIN rate ON (rate.comment_id = comment.id)
GROUP BY comment.id

- vearutop

Answer 2

您当前的查询将简单地对所有评论的评分进行求和。您需要做的是使用"GROUP BY"来按照每个评论进行聚合，因此"SUM()"的结果将仅为每个特定评论的评分总和，而不是对所有评论的总和：

SELECT a.*, SUM(b.score) AS ratingsum
FROM comment a
LEFT JOIN rate b ON a.id = b.comment_id
GROUP BY a.id

- Zane Bien

Answer 3

尝试添加一个group by子句：

 SELECT comment.*, SUM(rates.score) FROM comment LEFT JOIN rate ON rate.comment_id = comment.id GROUP BY rates.comment_id;

- Mike Purcell