mysql不同的选择查询

时间:2022-10-04 00:51:08

how can I have with one query the following: I would like to have from my comments table all the people how have been commenting on a given post_id and than check how many time the user has commented, based on his name. I would like to avoid to have 2 different queries for it

我要如何使用一个查询:我希望所有人都能从我的comments表中获得关于给定的post_id的评论,并根据用户的名字检查用户评论了多少次。我想避免对它有两个不同的查询

I have been trying the following but won't return to expected result

我一直在尝试,但不会回到预期的结果。

SELECT comments.*, COUNT(approved.comment_approved) AS has_commented  FROM wp_comments AS comments  
    INNER JOIN wp_comments AS approved 
    ON comments.comment_author = approved.comment_author

WHERE comments.comment_post_ID =14616
GROUP BY comments.comment_content

1 个解决方案

#1


3  

Shouldn't you group by post_ID ? (that would return only one line)

你不应该按post_ID分组吗?(只返回一行)

SELECT
  comments.*
, COUNT(approved.comment_approved) AS "has_commented"
FROM wp_comments AS comments
JOIN wp_comments AS approved
  ON (comments.comment_author = approved.comment_author)
WHERE comments.comment_post_ID = 14616
GROUP BY comments.comment_post_ID
;

Or do you want one line per "approved" comment ?

或者你想要每行“批准”的评论?

#1


3  

Shouldn't you group by post_ID ? (that would return only one line)

你不应该按post_ID分组吗?(只返回一行)

SELECT
  comments.*
, COUNT(approved.comment_approved) AS "has_commented"
FROM wp_comments AS comments
JOIN wp_comments AS approved
  ON (comments.comment_author = approved.comment_author)
WHERE comments.comment_post_ID = 14616
GROUP BY comments.comment_post_ID
;

Or do you want one line per "approved" comment ?

或者你想要每行“批准”的评论?