使用查询显示帖子的层次结构

Question

我正在构建一个简单的主题/讨论机制，但是我很难创建一个查询，以便像帖子树/层次结构一样检索我的帖子。

我有一个POSTS表，具有以下属性（简化）：id、text、username、type、replytoid。

类型：'THREAD START'或'REPLY' replytoid：评论正在回复另一个评论，因此我在这里存储“父”id。'THREAD START'具有空的replytoid。

我的测试数据是：(fiddle)

| ID |             TEXT | USERNAME |                 TYPE | REPLYTOID |
|----|------------------|----------|----------------------|-----------|
|  1 | My name is Alice |    Alice | THREAD START         |    (null) |
|  2 |  Reply to @Alice |      Bob | REPLY                |         1 |
|  3 |    Reply to @Bob |    Carol | REPLY                |         2 |
|  4 |  Reply to @Carol |     Dave | REPLY                |         3 |
|  5 |  Reply to @Alice |      Eve | REPLY                |         1 |
|  6 | My name is Frank |    Frank | THREAD START         |    (null) |
|  7 |  Reply to @Frank |     Gina | REPLY                |         6 |

我尝试了许多种JOIN操作，但是我无法得到我期望的输出结果，也就是：

| ID |             TEXT | USERNAME |            TYPE | REPLYTOID | LEVEL | THREADID |
|----|------------------|----------|-----------------|-----------|-------|----------|
|  1 | My name is Alice |    Alice | THREAD START    |    (null) |     1 |        1 |
|  2 |  Reply to @Alice |      Bob | REPLY           |         1 |     2 |        1 |
|  5 |  Reply to @Alice |      Eve | REPLY           |         1 |     2 |        1 |
|  3 |    Reply to @Bob |    Carol | REPLY           |         2 |     3 |        1 |
|  4 |  Reply to @Carol |     Dave | REPLY           |         3 |     4 |        1 |
|  6 | My name is Frank |    Frank | THREAD START    |    (null) |     1 |        6 |
|  7 |  Reply to @Frank |     Gina | REPLY           |         6 |     2 |        6 |

- Ricardo

---

你的意思是只需要添加Level和ThreadID吗？ - Meysam Tolouee

是的。@Szymon的回答帮了我。 - Ricardo

Answer 1

这是一个可以使用递归CTE的问题的很好的例子。以根帖子为锚点开始，并将回复链接到父级来构建。您可以通过增加先前级别并保留父ID来获取级别和线程。

with cte as
(
  select *, 1 as level, id as thread
  from posts
  where replytoid is null

  union all

  select posts.*, cte.level + 1 as level, cte.thread 
  from posts
  inner join cte on cte.id = posts.replytoid
)

select * from cte
order by thread, level

SQL Fiddle

的意思是保留HTML格式的文本，其中包含一个名为"SQL Fiddle"的链接。

- Szymon