MySQL 在求和之前检查值是否在结果集中

Question

我有一个评论评分表，当我获取评论时，我也会获取评分，我还希望能够显示登录用户已经对哪些评论进行了投票。这就是我现在所做的

SELECT 
c.id, 
c.text, 
c.datetime, 
c.author, 
u.email AS author_name, 
SUM(cr.vote) AS rating, 
cr2.vote AS voted

FROM comments c 
LEFT JOIN users u ON u.id = c.author 
LEFT JOIN comments_ratings cr ON c.id = cr.comment 
LEFT JOIN comments_ratings cr2 ON c.id = cr2.comment AND cr2.user = :logged_user_id 

GROUP BY c.id ORDER BY c.id DESC

但我不喜欢在同一张表上执行第二次联接的方式。我知道这是完全有效的，但如果我能从第一个连接中获得我想要的信息，那么为什么要执行第二个连接？

在执行聚合函数之前，是否有可能在表 comments_ratings cr 上确定列 user 等于 :logged_user_id 的行是否存在(s)?

P.S.:如果有人能想出一个更好的标题，将来人们可以找到，我也会很感激。

Answer 1

你可以用条件聚合做你想做的事:

SELECT c.id, c.text, c.datetime, c.author, u.email AS author_name, 
       SUM(cr.vote) AS rating, 
       MAX(cr.user = :logged_user_id) as voted
FROM comments c LEFT JOIN
     users u
     ON u.id = c.author LEFT JOIN
     comments_ratings cr
     ON c.id = cr.comment 
GROUP BY c.id
ORDER BY c.id DESC;