mysql - 如何选择帖子的最后编辑版本？

Question

表结构如下:

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 1  | content 1                   | NULL      |
| 2  | content 2                   | NULL      |
| 3  | content 1 (edited)          | 1         |
| 4  | content 3                   | NULL      |
| 5  | content 4                   | NULL      |
| 6  | content 4 (edited)          | 5         |
| 7  | content 1 (edited)          | 1         |
+----+-----------------------------+-----------+

现在我想选择每篇文章的最新编辑版本。所以这是预期的结果:

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 7  | content 1 (edited)          | 1         |
| 2  | content 2                   | NULL      |
| 4  | content 3                   | NULL      |
| 6  | content 4 (edited)          | 5         |
+----+-----------------------------+-----------+

我该怎么做？

Answer 1

SQL Fiddle 看看它是如何工作的。

首先，我们从 post_content 中删除 "(edited)" 字符串部分(在需要的地方)，为分组依据准备一列，然后计算最大值 id 每个我们的组，最后连接回同一个表以检索特定 id 的值。

SELECT
  goo.id,
  qa.post_content,
  qa.edited_id
FROM (
  SELECT
    MAX(id) AS id,
    post_content
  FROM (
    SELECT
      id,
      CASE WHEN locate(' (edited)', post_content) <> 0
           THEN left(post_content, locate(' (edited)', post_content) - 1)
           ELSE post_content
           END AS post_content,
      edited_id
    FROM qa
    ) foo
  GROUP BY post_content
  ) goo
  INNER JOIN qa ON goo.id = qa.id

输出

+----+-----------------------------+-----------+
| id |       post_content          | edited_id |
+----+-----------------------------+-----------+
| 7  | content 1 (edited)          | 1         |
| 2  | content 2                   | NULL      |
| 4  | content 3                   | NULL      |
| 6  | content 4 (edited)          | 5         |
+----+-----------------------------+-----------+

CASE 解释

-- if post_content contains " (edited)" then locate() will return value of it's position
CASE WHEN locate(' (edited)', post_content) <> 0
  -- we're removing the " (edited)" part by doing left()-1 because we want the string to finish before first character of " (edited)"
     THEN left(post_content, locate(' (edited)', post_content) - 1)
  -- if post_content doesn't contain " (edited)" then simply return post_content
     ELSE post_content
     END AS post_content