mysql - 使用连接在 mysql 中按值计数

Question

我有三个表:用户、主题和帖子，它们的结构如下:

--users
----id
----name
----email
----password
----profile_pic

--topics
----id
----topic_title
----topic_category
----topic_content
----author_id

--comments
----id
----topic_id
----author_id
----comment
----mood

这个的 sql fiddle 在:http://sqlfiddle.com/#!2/8e5241

现在我需要做的是查询所有主题并获取每个主题的作者信息以及每个主题的评论数。这个查询很容易做到:

SELECT 
  topics.id, 
  topics.topic_title, 
  topics.topic_category, 
  topics.topic_content, 
  topics.author_id, 
  users.name, 
  users.profile_pic, 
  topics.created_at, 
  count(comments.id) AS comments 
FROM 
  topics 
JOIN 
  users 
ON 
  users.id = topics.author_id 
LEFT JOIN 
  comments 
ON 
  topics.id = comments.topic_id 
GROUP BY 
  topics.id 
ORDER BY 
  topics.created_at 
DESC

它返回一个 sql 结果:

topic_title | created_at | id | topic_category | author_id | topic_content | name | profile_pic | comments

这很好用，问题是我不只需要总评论数。评论表中的心情字段可以有 3 个可能的值(0、1、2)，我需要计算每个值的评论数量。

我试着改变

count(comments.id)

在上面的查询中

count(comments.mood=0) AS happy, count(comments.mood=1) AS sad, count(comments.mood=2) AS angry

但是这会为每个结果字段返回相同的值。有没有办法在单个 mySQL 查询中执行此操作？

Answer 1

你需要使用 sum() 来做这样的操作

sum(comments.mood=0) as happy,
sum(comments.mood=1) as sad,
sum(comments.mood=2) as angry,

由@Pavel 编辑:我将分享我用来获得正确结果的最终查询，该查询基于@Abhik Chakraborty 和@Tomalak 给出的答案。

SELECT 
    topics.id, 
    topics.topic_title, 
    topics.topic_category, 
    topics.topic_content, 
    topics.author_id, 
    users.name AS author_name, 
    users.profile_pic, 
    topics.created_at, 
    IFNULL(SUM(comments.mood=0),0) AS comments_happy, 
    IFNULL(SUM(comments.mood=1),0) AS comments_sad, 
    IFNULL(SUM(comments.mood=2),0) AS comments_angry 
  FROM 
    topics 
  JOIN 
    users 
  ON 
    users.id = topics.author_id 
  LEFT JOIN 
    comments 
  ON topics.id = comments.topic_id 
  GROUP BY 
    topics.id 
  ORDER BY 
    topics.created_at 
  DESC