MySQL组之前的MySQL命令 [英] MySQL Order before Group by

问题描述

我需要为每个作者找到最新的帖子，然后对结果进行分组，以便每个作者只有一个最新帖子。

  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 
  

这是正确的分组输出，所以我只给每个作者一篇文章，但它是排序的这些结果在他们被分组之后，而不是他们被选中之前。 wp_posts。* from wp_posts


其中wp_posts.post_status ='publish'and wp_posts.post_type ='post'


group by wp_posts.post_author


拥有wp_posts .post_date = MAX（wp_posts.post_date）/ *只有每位作者的最新帖子* /


orde r by wp_posts.post_date desc

---

编辑：

经过一些评论后，我决定添加一些额外的信息。

我正在工作的公司也使用Postgres，特别是SQL Server。这个数据库不允许这样的查询。所以我知道还有其他方法可以做到这一点（我在下面写一个解决方案）。如果您未对投影中处理过的所有列进行分组或使用聚合函数，则您还必须知道自己做了什么。否则让它成为！

我选择了上面的解决方案，因为这是一个特定的问题。汤姆想在wordpress网站上为每个作者获取最近的帖子。在我看来，如果作者每秒发布超过一篇文章，分析忽略不计。 Wordpress甚至应该通过垃圾邮件双重检测来禁止它。我从个人经验中知道，使用MySQL做一个如此肮脏的团体在性能方面会有非常显着的好处。但是如果你知道你做了什么，那么你可以做到！我在专业上负责的应用中有这样的肮脏组。在这里，我有一些mio行的表，需要5-15秒而不是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 的帖子。即使在这里，至少不能保证你真的会得到最后一个。

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 DESC

This 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

---

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.

这篇关于MySQL组之前的MySQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！