我运行了一个基于@spencer7593和@raymondnijland的两个答案的查询基准。

左加入获胜!

1。使用左连接

SELECT c.id, COUNT(DISTINCT v1.id) as upvotes, COUNT(DISTINCT v2.id) as userUpvoted, COUNT(DISTINCT v3.id) as downvotes, COUNT(DISTINCT v4.id) as userDownvoted FROM talk_comments c
  LEFT JOIN talk_comment_votes v1 ON v1.comment_id = c.id AND v1.status = 1 
    LEFT JOIN talk_comment_votes v2 ON v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 AND v2.is_anonymous = 0
    LEFT JOIN talk_comment_votes v3 ON c.id = v3.comment_id AND v3.status = 2
    LEFT JOIN talk_comment_votes v4 ON c.id = v4.comment_id AND v4.status = 2 AND v4.user_id = 1 AND v4.is_anonymous = 0
WHERE c.id = 2 GROUP BY c.id

1000次查询的时间: 0.55000805854797秒

2。使用子查询

SELECT c.id,c.user_id, c.time,c.body, c.reply_to, 
    (SELECT COUNT(v1.id) FROM talk_comment_votes v1 WHERE v1.comment_id = c.id AND v1.status = 1 LIMIT 1) as upvotes, 
    (SELECT COUNT(v2.id) FROM talk_comment_votes v2 WHERE v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 LIMIT 1) as clientUpvoted,
    (SELECT COUNT(v3.id) FROM talk_comment_votes v3 WHERE v3.comment_id = c.id AND v3.status = 2 LIMIT 1) as downvotes, 
    (SELECT COUNT(v4.id) FROM talk_comment_votes v4 WHERE v4.comment_id = c.id AND v4.status = 2 AND v4.user_id = 1 LIMIT 1) as clientDownvoted
      FROM talk_comments c 
           WHERE c.id = 2 GROUP BY c.id

1000次查询的时间: 0.95499300956726秒

三。使用sum,如果

SELECT c.id
     , SUM(IF(v.status = 1                                          ,1,0)) AS upvotes
     , SUM(IF(v.status = 1 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userUpvoted
     , SUM(IF(v.status = 2                                          ,1,0)) AS downvotes
     , SUM(IF(v.status = 2 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userDownvoted
    FROM talk_comments c
    LEFT
    JOIN talk_comment_votes v
      ON v.comment_id = c.id
   WHERE c.id = 2
   GROUP BY c.id

1000次查询的时间: 1.2266919612885秒

谢谢你的回答。