php - 中级 mySQL 查询 - 将两个表连接在一起并限制结果

Question

这是我的 table 安排:

Posts
   post_id
   post_votes

Comments
   comment_id
   post_id
   comment_time

我无法创建执行以下操作的查询:

1. 选择 10 个帖子 按 post_votes desc 排序
2. 每条帖子获得 5 条评论

如果有必要，我会发布我尝试过的内容。我刚刚遇到更复杂的查询，非常感谢任何帮助或建议

Answer 1

下面将分别按 desc 检索 10 个帖子顺序和按 desc 顺序检索 5 个评论。

select post_id,post_votes,comment_id,comment_time,
 @rn := CASE WHEN @prev_post_id = post_id THEN @rn + 1 ELSE 1 END AS rn,
        @prev_post_id := post_id from
  (select p.post_id,post_votes,comment_id,comment_time from
  (SELECT post_id,post_votes from posts order by post_votes desc limit 10) p 
   left outer join 
   comments c on p.post_id=c.post_id order by post_id,comment_time desc )m
having rn<=5

SQL FIDDLE HERE (测试示例按 desc 检索 3 个帖子订单以及每个帖子分别检索 2 个评论)。