mysql - sql查询 - 结果错误

Question

我有两个表，其中一个包含评论，另一个包含每个评论的评分。这是查询:

SELECT c.id,
       c.parent_id,
       c.name,
       c.body AS comment,
       c.user_id, 
       DATE_FORMAT( c.date_add, '%d %M %Y %H:%i') AS dt,
       c.deleted, 
       IFNULL( SUM( r.rate ), 0 ) AS rate
FROM comments AS c
LEFT JOIN
(
    SELECT SUM(rating) AS rate, 
           comment_id AS comment_id
    FROM rating
    GROUP BY comment_id
) r ON c.id = r.comment_id;

表格内容:

table Comments:
id
40
41
42

table Rating:
comment_id  Rating
41            -1
42             1

查询应返回:

id     Rating
41       -1
42        1
43        0

返回:

id     Rating
41        2

请有人告诉我错误在哪里。

Answer 1

您可以尝试以下版本吗？

SELECT c.id, 
       c.parent_id,
       c.name,
       c.body AS comment,
       c.user_id,
       DATE_FORMAT( c.date_add, '%d %M %Y %H:%i'),
       c.deleted,
       (SELECT SUM(rating) FROM rating r Where r.comment_id = c.id) AS rate
FROM comments AS c;