我不确定这是否可行,但在做“丑陋”的方式之前我需要确认:)
因此,“结果”是数据库中的帖子,存储方式如下：
> posts表,其中包含所有重要的内容,如ID,标题,内容
>后元表,其中包含额外的帖子数据,如评级(this_rating)和投票数(this_num_votes).该数据成对存储,该表有3列：帖子ID /键/值.它基本上是wordpress表结构.
我想要的是取出评分最高的帖子,根据这个公式排序：
br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) )
/ (avg_num_votes +this_num_votes)
我偷了here表格.
avg_num_votes和avg_rating是已知变量(它们在每次投票时都会更新),因此不需要计算它们.
最佳答案
数据堆栈交换链接：
http://data.stackexchange.com/stackoverflow/s/2137/order-database-results-by-bayesian-rating
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,this_num_votes,this_rating
ORDER BY br DESC