MySQL在与自身连接表时获得不同的ID

Question

我试着教自己一些新的东西，看看WordPress结构的方式，它的表与修订一起工作。

结构如下(排序)：

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrr     | 2012-09-20 10:05:00 | Arrrr      | Pirates!        |           0 |
|  3 | Arrrrrr    | 2012-09-20 10:06:00 | revision-1 | Argh pirates    |           2 |
|  4 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
+----+------------+---------------------+------------+-----------------+-------------+

现在，我想执行一个查询，它只给出父行(Foo和Arrrr)及其ID，但包含修改后的内容。

我提出了以下查询：

SELECT original.ID, revision.post_title, revision.post_content, revision.post_name FROM wp_posts AS original
INNER JOIN wp_posts AS revision ON original.ID = revision.post_parent
WHERE original.post_status = 'publish'
AND original.post_parent = 0
ORDER BY original.ID, revision.ID DESC

这给了我以下结果：

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-1 | Argh pirates    |           2 |
|  2 | Arrrrr     | 2012-09-20 10:05:00 | Arrrr      | Pirates!        |           0 |
+----+------------+---------------------+------------+-----------------+-------------+

但我想进一步将其简化为：

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
+----+------------+---------------------+------------+-----------------+-------------+

我尝试过在SELECT中添加DISTINCT和添加GROUP BY original.ID，但都没有得到想要的结果。

Answer 1

虽然除了日期之外，您无法区分同一主题的不同版本，但您可以执行以下操作：

select * from wp_posts p join
                (
                    select max(post_date) as max_dtm, id
                    from wp_posts
                    group by if(post_parent = 0, id, post_parent)
                ) v on p.id = v.id and p.post_date = v.max_dtm;