MySQL-由10位最新作者中的每位作者选择最新帖子 [英] MySQL - Selecting the most recent post by each of the 10 most recent authors

问题描述

我有一个表格，其中包含许多不同作者的博客文章.我想做的是显示10位最新作者中每位作者的最新文章.

I have a table containing blog posts by many different authors. What I'd like to do is show the most recent post by each of the 10 most recent authors.

每个作者的帖子只是简单地按顺序添加到表中，这意味着单个作者可能会运行多个帖子.我花了很多时间提出一个查询来做到这一点.

Each author's posts are simply added to the table in order, which means there could be runs of posts by a single author. I'm having a heck of time coming up with a single query to do this.

这给了我最后10个唯一的作者ID；可以作为子选择来获取每个作者的最新帖子吗?

This gives me the last 10 unique author IDs; can it be used as a sub-select to grab the most recent post by each author?

SELECT DISTINCT userid
FROM posts
ORDER BY postid DESC 
LIMIT 10

推荐答案

select userid,postid, win from posts where postid in (
SELECT max(postid) as postid
FROM posts 
GROUP BY userid
) 
ORDER BY postid desc 
limit 10

http://sqlfiddle.com/#!2/09e25/1

这篇关于MySQL-由10位最新作者中的每位作者选择最新帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！