mysql - MySQL |左连接

我有这样的东西：

ID | post_author | post_date | ... | post_title | post_status | ... | post_type
-------------------------------------------------------------------------------
1  | 1           | 2007-08-11| ... | A          | publish     | ... | post
2  | 3           | 2007-08-12| ... | B          | publish     | ... | post
3  | 1           | 2007-08-12| ... | C          | publish     | ... | post
4  | 1           | 2007-08-13| ... | D          | publish     | ... | post
5  | 3           | 2007-08-13| ... | E          | publish     | ... | post

我喜欢做的是按用户获取文章的数量以及最后一篇文章的标题和id。根据上述数据，结果应为：

AuthorID | TotalPosts | PostID | PostTitle
------------------------------------------
1        | 3          | 5      | E
3        | 2          | 4      | D

我试过的是：

SELECT
    p1.post_author         AS  Author,
    count(p1.post_author)  AS  Posts,
    p2.post_title          AS  Title
FROM
    wp_posts AS p1
LEFT JOIN
    wp_posts As p2
ON
    p1.ID = p2.ID
WHERE
    p1.post_type   =   'post'
AND
    p1.post_status =   'publish'
GROUP BY
    p1.post_author
ORDER BY
    Posts   DESC,
    p2.post_date   ASC
LIMIT
    2

问题是我总是得到第一篇文章的标题，而不是最后一篇文章的标题。有没有办法得到最后一篇文章的标题？
亲切的问候

最佳答案

如果只需要每个作者的最新标题，则可以使用内联关联子查询。使用适当的索引，这将相对较快—特别是如果您只希望获得两个大多数已发布作者的结果：

SELECT
    post_author         AS  Author,
    COUNT(*)            AS  Posts,
    ( SELECT   p2.post_title
      FROM     wp_posts AS p2
      WHERE    p2.post_author = p1.post_author
        AND    p2.post_type = 'post'
        AND    p2.post_status = 'publish'
      ORDER BY p2.post_date DESC
        LIMIT 1
    )                   AS  Title
FROM
    wp_posts AS p1
WHERE
    post_type   =   'post'
AND
    post_status =   'publish'
GROUP BY
    post_author
ORDER BY
    Posts   DESC
LIMIT
    2 ;

关于mysql - MySQL |左连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14836094/