MySQL COUNT函数不像我想在多个连接查询中执行 [英] MySQL COUNT function not performing as I would like in multiple joined query

问题描述

我已经改变一个查询了一段时间，阅读这个奇妙的网站上的许多帖子到我到目前为止的位置。但是，唉，现在我被困住了：（

I have been altering a query for a while now, reading many posts on this wonderful site to get to where I am with this so far. But, alas, now I am stuck :(

这个查询和我设计的数据库的相关部分类似于youtube评论喜欢系统相关的表和字段：

This query and relevant part of the database I have designed is similar to the youtube comment liking system. Relevant tables and fields are:

USRS

usr_id int（PK）

USRS
usr_id int (PK)

注释

comment_id int（PK）
usr_id int（FK）引用usrs（usr_id）
topic_id int（FK）引用主题（topic_id）
descr varchar
created varchar

COMMENTS
comment_id int (PK) usr_id int (FK) references usrs(usr_id) topic_id int (FK) references topics(topic_id) descr varchar created varchar

COMMENT_LIKERS

comment_id int（PK）（FK）引用注释（comment_id）
usr_id int（PK）（FK）引用usrs （usr_id）
liker tinyint

COMMENT_LIKERS
comment_id int (PK) (FK) references comments(comment_id) usr_id int (PK) (FK) references usrs(usr_id) liker tinyint

我想在一个查询中选择所有相关数据。除了每个注释的一般数据外，我想计数所有的喜欢和不喜欢的每个评论。我到目前为止的查询计数所有评论的喜欢，而不是每个，即使我有LEFT JOIN与ON子句：comment.comment_id = comment_likers.comment_id。

I want to be able to select all relevant data in one query. Aside from general data for each comment, I want to count all likes and dislikes for each comment. The query I have thus far is counting the likes for ALL comments and not for each even though I have the LEFT JOIN with ON clause: comments.comment_id = comment_likers.comment_id.

我正在学习MySql和PHP，所以如果我做了一些蠢事，我很容易。

I am learning MySql and PHP so go easy with me if I have done something silly. I assure you I have looked all around for clues to the answer.

这里是查询：

SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name,
  COUNT(if(comment_likers.liker = 1, 1, null)),
  COUNT(if(comment_likers.liker = 0, 1, null)),
  comment_likers2.liker
FROM comments
INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id )
LEFT JOIN comment_likers ON ( comments.comment_id = comment_likers.comment_id )
LEFT JOIN comment_likers AS comment_likers2 ON ( comments.comment_id = comment_likers.comment_id AND comment_likers.usr_id = $usrID )
WHERE comments.topic_id = $tpcID
GROUP BY comments.comment_id
ORDER BY comments.created DESC

提前感谢

推荐答案

SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name, 
  (SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=1)likes,
  (SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=0)dislikes
  liker
FROM comments
INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id )
LEFT JOIN comment_likers  ON ( comments.comment_id = comment_likers.comment_id 
 AND comment_likers.usr_id = $usrID )
WHERE comments.topic_id=$tpcID
ORDER BY comments.created DESC;

一对夫妇笔记。我不太确定什么第二左连接上comment_likers应该完成（使用$ usrID）。

A couple notes. I wasn't too sure what the second left join on comment_likers was supposed to accomplish (the one using $usrID). Are you only interested in likes from on a specific topic from a specific user?

此外，您可以考虑更改注释的架构 created 为datetime，而不是varchar。

Also, you might think about changing the schema for comments created to be a datetime instead of a varchar.

这篇关于MySQL COUNT函数不像我想在多个连接查询中执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！