合并来自不同表的两个SELECT查询

Issue

I have one table (posts) with articles and article meta.

我有一个表(帖子)与文章和文章元。

Another table (post_reviews) contains user-submitted ratings (a value out of 5) for each article, referencing posts by the id of the post in question.

另一个表(post_reviews)包含每篇文章的用户提交的评级(5分之一的值),通过相关帖子的ID引用帖子。

I am trying to find the top three posts, by review, of the last 3 days. Therefore I need to:

我试图在最近3天内通过审核找到前三个帖子。因此我需要:

find all the posts in that time period (3 days)

找到该时间段内的所有帖子(3天)

find the average rating for each post

找到每个帖子的平均评分

sort them by average rating (desc)

按平均评分(desc)排序

Code

For the first part, I can successfully use the query:

对于第一部分,我可以成功使用查询:

SELECT * FROM `posts` WHERE `hub_id`=:hub_id AND `date`>=:start_date AND `date`<=:end_date)

To find each individual post's average rating, I use this query:

要查找每个帖子的平均评分,我使用此查询:

SELECT SUM(`review`) AS `total` FROM `post_reviews` WHERE `id`=:id

then get the number of rows from this to work out the average:

然后从中获取行数以计算出平均值:

SELECT * FROM `post_reviews` WHERE `post_id`=:id

How can I combine these three, or process this data so I can order the posts in a time period by the average rating?

如何组合这三个,或者处理这些数据,以便我可以按照平均评级在一段时间内订购帖子?

ANSWER

The end result looks like this:

最终结果如下:

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

3 个解决方案

#1

Not sure what your hub_id represents, but I assume it's necessary; also assume the key field in Posts is posts.post_id and not posts.id:

不确定你的hub_id代表什么,但我认为这是必要的;还假设帖子中的关键字段是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;

See Example: sqlfiddle

参见示例:sqlfiddle

#2

Not sure about the syntax for MySQL, but the would need a join and a group by. Something like....

不确定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

#3

Here is a query that works with sqlfiddle to prove it.

这是一个与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

#1