问题
我有一张表(posts
),里面有文章和文章元。
另一个表(post_reviews
)包含用户提交的每篇文章的评分(值为5),引用了相关文章的id
中的posts
。
我正在努力寻找前三个帖子,通过审查,过去三天。因此,我需要:
- 查找该时间段(3天)内的所有帖子
- 找到每个帖子的平均评分
- 按平均评分(desc)排序
代码
对于第一部分,我可以成功地使用查询:
SELECT * FROM `posts` WHERE `hub_id`=:hub_id AND `date`>=:start_date AND `date`<=:end_date)
为了找到每个帖子的平均评分,我使用以下查询:
SELECT SUM(`review`) AS `total` FROM `post_reviews` WHERE `id`=:id
然后从中得到行数来计算平均值:
SELECT * FROM `post_reviews` WHERE `post_id`=:id
我如何将这三者结合起来,或者处理这些数据,以便我可以根据平均评分在一段时间内对帖子进行排序?
答案
最终结果如下:
SELECT `posts`.`id`, avg(`post_reviews`.`review`) as `average`
FROM `posts`
JOIN `post_reviews` ON (`posts`.`id`=`post_reviews`.`post_id`)
WHERE `hub_id`=:hub_id
AND `posts`.`date`>=:start_date
AND `posts`.`date`<=:end_date
GROUP BY `post_id`
ORDER BY avg(`review`) desc
不确定hub_id
代表什么,但我认为这是必要的;还假设Posts中的关键字段是posts.post_id
,而不是posts.id
:
SELECT `p`.`id`, avg(`pr`.`review`) AS `average`
FROM `posts` AS `p`
JOIN `post_reviews` AS `pr` ON (`p`.`id`=`pr`.`post_id`)
WHERE `hub_id` =:hub_id
AND `p`.`date` BETWEEN CURRENT_DATE-3 AND CURRENT_DATE
GROUP BY `p`.`id`
ORDER BY avg(`review`) DESC;
请参阅示例:sqlfiddle
不确定MySQL的语法,但需要一个join和一个group-by。类似。。。。
SELECT post_id. avg(review)
FROM Posts P Inner Join Post_reviews PR on (p.post_id = pr.Post_id)
WHERE `hub_id`=:hub_id AND `date`>=:start_date AND `date`<=:end_date)
Group by Post_id
order by 2 desc
下面是一个使用sqlfiddle的查询来证明它
SELECT
p.hub_id
,p.post_id
,p.article
,p.articleMeta
,p.date
,IFNULL(AVG(r.ratings), 0) averageRating
FROM posts p
LEFT JOIN post_reviews r ON
r.post_id = p.post_id
WHERE
hub_id = 1
AND date >= CURDATE() - 3
AND date <= CURDATE()
GROUP BY
p.hub_id
,p.post_id
,p.article
,p.articleMeta
,p.date
ORDER BY
p.date DESC
,averageRating DESC
http://sqlfiddle.com/#!2/39315/1