mysql获取记录深度，计算父记录和祖先记录

Question

假设我有一个包含字段 post_id 和 parent_post_id 的帖子表。我想返回帖子表中的每条记录以及帖子的“深度”计数。我所说的深度是指存在多少父母和祖先记录。

以这个数据为例...

post_id   parent_post_id
-------   --------------
1         null
2         1
3         1
4         2
5         4

数据代表这个层次结构...

1
|_ 2
|  |_ 4
|     |_ 5
|_ 3

查询的结果应该是...

post_id   depth
-------   -----
1         0
2         1
3         1
4         2
5         3

提前感谢！

Answer 1

如果您进行大量这样的查询，您可能会发现嵌套集模型比您要查询的邻接列表更合适。 此处对这两种模型进行了很好的讨论。

无论如何，要完成您对邻接列表的要求，您要么在应用程序层中进行递归，要么将级别存储为第三列。

ETA：如果您的级别计数不是很高，您可以使用自连接来实现：

例如具有 2 个祖先的节点：

SELECT t1.node 
FROM mytable AS t1
JOIN mytable AS t2 ON t1.parent = t2.node
JOIN mytable AS t3 ON t2.parent = t3.node
WHERE t3.parent IS NULL;