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

我有一个像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         |
+----+---------+---------------------------+---------+------+-----------+

栏目说明：

related column:

• NULL对于问题和问题/答案的编辑版本
• {the id of its own question}寻求答案

type column:

• 0如有疑问
• 1寻求答案

edited_id column: （原帖id）

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

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

示例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

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

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

您可以尝试以下查询：

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 http://sqlfiddle.com/#!9/2bd2cc/2/1