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

Question

我有一个像 SO 这样的问答网站。我还有一个表格，其中包含问题和答案及其编辑版本。这是我的表结构:

// 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         |
+----+---------+---------------------------+---------+------+-----------+

列说明:

相关专栏:

• NULL 问题和问题/答案的编辑版本
• {自己问题的id}寻找答案

类型列:

• 0 问题
• 1 用于回答

edited_id 列: (原始帖子的 id)

• NULL 表示这是一个原始问题/答案
• {any number} 表示它是问题/答案的编辑版本。

---

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

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

+----+---------+---------------------------+---------+------+-----------+
| 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      |
+----+---------+---------------------------+---------+------+-----------+

示例 2: 我有这个值::id = 3 我想要这个输出:

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

示例 2: 我有这个值::id = 7 我想要这个输出:

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

---

这是我当前的查询:

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

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

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

Answer 1

您可以尝试以下查询:

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

WORKING DEMO