MySQL | 左连接

Question

我有一个类似于这样的东西：

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

问题在于我总是获取第一篇文章的标题，而不是最后一篇文章的标题。有没有一种方法可以获取最后插入的文章的标题？

此致敬礼

- KodeFor.Me

Answer 1

如果您只想获取每个作者的最新标题，可以使用内联相关子查询。如果有适当的索引，则速度相对较快，特别是如果您只需要前两个发表最多的作者的结果：

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 ;

- ypercubeᵀᴹ

---

相关子查询中有一个错误，已经被纠正。 - ypercubeᵀᴹ

我会尝试在子查询中摆脱ORDER BY。 - Bulat

ORDER BY p2.post_date DESC 实际上在那里是不必要的。它可以被替换为 AND p2.post_date = MAX(p1.post_date) ORDER BY anything。但我没有看到任何避免使用 ORDER BY 的理由。 - ypercubeᵀᴹ

ORDER BY会增加额外的处理成本。排序的成本是Theta(N^2)，而最大值的成本是Theta(N)。除非我漏掉了什么。 - Bulat

Answer 2

这样的代码应该可以正常工作：

SELECT p.post_author, p3.id, p3.post_title, COUNT(DISTINCT p.id) PostCount
FROM wp_posts p
   JOIN (
    SELECT Max(Post_Date) max_post_date, post_author
    FROM wp_posts
    GROUP BY post_author) p2
     ON p.post_author = p2.post_author
   JOIN wp_posts p3 on p.post_author = p3.post_author
    AND p2.max_post_date = p3.post_date
GROUP BY p.post_author, p3.id, p3.post_title

这里假设每篇文章只有一个日期。如果不是这样，那么你的ID字段是否也是最大字段呢？

- sgeddes

Answer 3

你能检查一下这个选项是否更快吗:

SELECT t1.*, t2.post_title FROM
    (SELECT 
        post_author         AS  Author,
        COUNT(*)            AS  Posts,
        Max(ID) AS MaxID
    FROM 
        wp_posts AS p
    WHERE
        post_type   =   'post' AND
        post_status =   'publish'
    GROUP BY post_author
    ORDER BY Posts   DESC
    LIMIT 2) t1 LEFT JOIN wp_posts t2 
                 ON t1.MaxID= t2.ID

- Bulat

---

MAX(id) 不等同于 MAX(post_date)。 - ypercubeᵀᴹ

@ypercube 嗯，这取决于具体实现，毫无疑问。但我相信即使ID和post_date没有相关性，也一定有一种方法可以在不使用ORDER BY的情况下完成这个任务。 - Bulat

Answer 4

ORDER BY
p2.post_date DESC,
Posts   DESC

- Sergey Onishchenko