包括用户评论过的帖子

本文介绍了包括用户评论过的帖子的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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