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

Question

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

我有一个具有以下属性(简化)的 POSTS 表:id、text、username、type、replytoid

类型:“线程开始”或“回复”
回复:该评论正在回复另一条评论，所以我在这里存储了“父”ID。 'THREAD START' 有一个 NULL 回复。

我的测试数据是:( 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 |

Answer 1

这是您可以使用递归 CTE 的问题的一个很好的例子。从根帖子作为 anchor 开始，并建立将它们链接到 parent 的回复。您可以通过保留父 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