php - MySQL 选择分组方式显示最新帖子

Question

$postids = $wpdb->get_results( 
    "
    SELECT ID, post_title, post_author, max(post_date)
    FROM $wpdb->posts
    WHERE post_status = 'publish' 
          AND post_type = 'post'
    GROUP BY post_author
    ORDER BY post_date DESC
    "
);

foreach ( $postids as $postid ) 
{
    echo $postid->ID." :: ".$postid->post_title." :: ".$postid->post_author . "<br />" ;
}

我想从每位作者中选择一篇最新的帖子，但上面的代码仍然选择了每位作者中最旧的帖子。 max(post_date) 似乎不起作用。有什么建议可以使这项工作成功吗？

Answer 1

您首先需要找出每位作者最近发表的文章的日期。

         SELECT max(post_date) post_date, post_author
           FROM $wpdb->posts
          WHERE post_status = 'publish' 
           AND post_type = 'post'
         GROUP BY post_author

然后您需要使用此结果来提取所需帖子的详细信息。

SELECT a.ID, a.post_title, a.post_author, a.post_date
 FROM $wpdb->posts a
 JOIN (
         SELECT max(post_date) post_date, post_author
           FROM $wpdb->posts
          WHERE post_status = 'publish' 
           AND post_type = 'post'
         GROUP BY post_author
      ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
 WHERE post_status = 'publish' 
   AND post_type = 'post'
 ORDER BY post_date DESC

当您为此目的工作时，

GROUP BY 有点棘手。

注意:我没有在 WordPress 环境中调试过这个查询。

请注意，如果作者设法同时创建两个帖子，则它们的 post_date 值都将与 MAX(post_date) 值相匹配。在这种情况下，此查询会同时返回它们。