如何选择帖子的最后编辑版本？ [英] How can I select the last edited version of the post?

问题描述

我有一个类似SO的问答网站。我还有一张表格，其中包含问题和答案以及它们的编辑版本。这是我的表结构：

I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1  | title1  | question content          | NULL    | 0    | NULL      |
| 2  |         | answer content            | 1       | 1    | NULL      | 
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
| 5  |         | answer content            | 1       | 1    | NULL      |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 7  | title3  | question content          | NULL    | 0    | NULL      |
| 8  | title1  | question content (edited) | NULL    | 0    | 1         |
| 9  |         | answer content            | 7       | 1    | NULL      |
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
+----+---------+---------------------------+---------+------+-----------+

列说明：

相关列：

Column explanations:

related column:

• NULL 对于问题和问题/答案的编辑版本


• {自己问题的ID}

• NULL for both questions and edited version of questions/answers
• {the id of its own question} for answers

type 列：

type column:

• 0 提问


• 1 回答

• 0 for questions
• 1 for answers

edited_id 列： （原始帖子的ID）

• NULL 表示这是原始问题/答案


• {任何数字} 表示它是问题/答案的编辑版本。

• NULL means it is a original question/answer
• {any number} means it is a edited version of a question/answer.

现在，我需要一个查询来选择一个问题及其所有答案。请注意，我需要选择它们的最后个编辑版本（如果它们已被编辑）。

Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).

示例1：，我有这个值：：id = 1 ，我想要这个输出：

Example1: I have this value: :id = 1 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 5  |         | answer content            | 1       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2：：我有这个值：：id = 3 ，我想要这个输出：

Example2: I have this value: :id = 3 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2 ：我有这个值：：id = 7 ，我想要这个输出：

Example2: I have this value: :id = 7 and I want this output:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
| 9  |         | answer content            | 7       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

---

这是我当前的查询：

---

Here is my current query:

SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
      (related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter

如您所见，我的查询不支持编辑版本。无论如何，当帖子有编辑行时，该如何替换帖子的编辑版本？

As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?

注意：请不要告诉我不要将问题和答案都放在同一张表中 ，因为我知道。但是，现在我需要解决上面的问题。

Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.

推荐答案

您可以尝试以下查询：

SET @qid := 1;

SELECT 
QA.*
FROM QA 
INNER JOIN 
(
    SELECT 
    MAX(GREATEST(A.id, COALESCE(B.id, 0))) latest_id
    FROM QA A 
    LEFT JOIN QA B ON A.id = B.edited_id 
    WHERE @qid IN(A.id,A.related,A.edited_id)
    GROUP  BY A.type , IF(A.type = 1, A.id,0)
) AS t 
ON QA.id = t.latest_id

工作演示

这篇关于如何选择帖子的最后编辑版本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！