包括用户评论过的帖子 [英] Including posts that users have commented on

问题描述

我正在计算撰写帖子的用户的平均分.但是，现在我也想平均他们评论的帖子的分数.我的查询(不包含评论)如下所示:

I am calculating average points for users that authored a post. However, now I want to average in the points for the posts that they commented on too. My query, without including comments looks like this:

SELECT u.user_fullname, ROUND(AVG(p.total_points),2) avgPoints
FROM cl_user_identities u
JOIN cl_posts p ON p.user_identity_id = u.user_identity_id
GROUP BY u.user_identity_id

如何添加用户也评论过的帖子?

How do I add in the posts that users have commented on too?

这是我的表架构:

cl_posts
 - post_id
 - user_identity_id
 - post_title
 - total_points

cl_comments
 - comment_id
 - post_id
 - user_identity_id
 - comment_text

cl_user_identities
 - user_identity_id
 - user_fullname

任何帮助都会很棒！

推荐答案

SELECT u.user_fullname, 
 ROUND(AVG(p.total_points),2) avgPoints,
 (SELECT ROUND(AVG(total_points),2) FROM cl_posts p2 JOIN cl_comments c2 ON c2.post_id = p2.post_id WHERE c2.user_identity_id = u.user_identity_id) as avgPoints2
FROM cl_user_identities u
JOIN cl_posts p ON p.user_identity_id = u.user_identity_id
GROUP BY u.user_identity_id   

这篇关于包括用户评论过的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！