我需要找到每个作者的最新帖子，然后分组结果，所以我只为每个作者一个最新的帖子。
SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author ORDER BY wp_posts.post_date DESC这是正确的分组输出，所以我只得到一个作者的一个帖子，但它是排序的结果，他们被分组后，而不是在他们被选定之前。
I need to find the latest post for each author and then group the results so I only a single latest post for each author.
SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post' GROUP BY wp_posts.post_author ORDER BY wp_posts.post_date DESCThis is correctly grouping the output so I only get one post per author, but it is ordering the results after they have been grouped and not before they have been selected.
最满意答案
select wp_posts.* from wp_posts where wp_posts.post_status='publish'and wp_posts.post_type='post' group by wp_posts.post_author having wp_posts.post_date = MAX(wp_posts.post_date) /* ONLY THE LAST POST FOR EACH AUTHOR */ order by wp_posts.post_date desc
编辑：
经过一些评论，我决定添加一些额外的信息。
我正在工作的公司也使用Postgres，特别是SQL Server。 此数据库不允许这样的查询。 所以我知道还有一种方法来做到这一点（我在下面写下一个解决方案）。 如果您不按照投影中所处理的所有列进行分组，或者使用聚合函数，您也必须知道自己的工作。 否则让它成为！
我选择了上面的解决方案，因为这是一个具体的问题。 汤姆想在wordpress网站上获得每位作者的最近的帖子。 在我看来，如果作者每秒钟多出一个帖子，分析可以忽略不计。 Wordpress应该甚至禁止它的垃圾邮件双重检测。 我从个人经验中知道，使用MySQL，这样一个肮脏的组织的表现有很大的好处。 但是如果你知道你做什么，那么你可以做到！ 我在专业负责的应用程序中有这么脏的组织。 这里我有一些mio行的表需要5-15s而不是100 ++秒。
可能对一些利弊有用： http : //ftp.nchu.edu.tw/MySQL/tech-resources/articles/debunking-group-by-myths.html
SELECT wp_posts.* FROM wp_posts JOIN ( SELECT g.post_author MAX(g.post_date) AS post_date FROM wp_posts as g WHERE g.post_status='publish' AND g.post_type='post' GROUP BY g.post_author ) as t ON wp_posts.post_author = t.post_author AND wp_posts.post_date = t.post_date ORDER BY wp_posts.post_date
但是如果这样一来，对于一个作者来说，这里每秒还会有一个帖子，你将获得超过一行而不是最后一行 。
现在你可以再次旋转轮子，并获得最高Id的职位。 即使在这里，至少不能保证你真的得到最后一个。
select wp_posts.* from wp_posts where wp_posts.post_status='publish'and wp_posts.post_type='post' group by wp_posts.post_author having wp_posts.post_date = MAX(wp_posts.post_date) /* ONLY THE LAST POST FOR EACH AUTHOR */ order by wp_posts.post_date desc
EDIT:
After some comments I have decided to add some additional informations.
The company I am working at also uses Postgres and especially SQL Server. This databases don't allow such queries. So I know that there is a other way to do this (I write a solution below). You shoud also have to know what you do if you don't group by all columns treated in the projection or use aggregate functions. Otherwise let it be!
I chose the solution above, because it's a specific question. Tom want to get the recent post for each author in a wordpress site. In my mind it is negligible for the analysis if a author do more than one post per second. Wordpress should even forbid it by its spam-double-post detection. I know from personal experience that there is a really significant benefit in performance doing a such dirty group by with MySQL. But if you know what you do, then you can do it! I have such dirty groups in apps where I'm professionally accountable for. Here I have tables with some mio rows which need 5-15s instead of 100++ seconds.
May be useful about some pros and cons: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/debunking-group-by-myths.html
SELECT wp_posts.* FROM wp_posts JOIN ( SELECT g.post_author MAX(g.post_date) AS post_date FROM wp_posts as g WHERE g.post_status='publish' AND g.post_type='post' GROUP BY g.post_author ) as t ON wp_posts.post_author = t.post_author AND wp_posts.post_date = t.post_date ORDER BY wp_posts.post_date
But if here is more then one post per second for a author you will get more then one row and not the only last one.
Now you can spin the wheel again and get the post with the highest Id. Even here it is at least not guaranteed that you really get the last one.
更多推荐
发布评论