本文介绍了包括用户评论过的帖子的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在计算撰写帖子的用户的平均分.但是,现在我也想平均他们评论的帖子的分数.我的查询(不包含评论)如下所示:
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更多推荐
包括用户评论过的帖子
发布评论