该博客探讨了一种数据堆栈交换链接的查询方法，通过计算平均投票数和平均评分的加权平均来确定帖子的综合评分。文章详细介绍了如何使用LEFT JOIN和子查询从postmeta表中获取每个帖子的投票数和评分，并根据这些信息进行聚合和排序，以找到最高评分的帖子。
数据堆栈交换链接：
SELECT id,title,( AVG(this_num_votes) * AVG(this_rating) + this_num_votes * this_rating )
/ ( AVG(this_num_votes) + this_num_votes ) as br
FROM posts
LEFT JOIN (
SELECT DISTINCT post_id,
(SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_num_votes') as this_num_votes,
(SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_rating') as this_rating
FROM postmeta pm ) as newmeta ON posts.ID = newmeta.post_id
GROUP BY id,title,this_num_votes,this_rating
ORDER BY br DESC
1406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
