mysql获得记录深度，计数父记录和祖先记录 [英] mysql to get depth of record, count parent and ancestor records

问题描述

假设我有一个包含字段 post_id 和 parent_post_id 的post表。我想返回邮政表中的每个记录与邮政的深度的计数。

Say I have a post table containing the fields post_id and parent_post_id. I want to return every record in the post table with a count of the "depth" of the post. By depth, I mean, how many parent and ancestor records exist.

以此数据为例...

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

提前感谢！

推荐答案

如果你进行大量的查询，你可能会发现嵌套集模型比你所问的邻接表更合适。 这里有两个模型的良好讨论。

If you're making a lot of queries like this, you may find that a nested set model is more appropriate than the adjacency list you're asking about. There's a good discussion of both models here.

在任何情况下，要使用邻接列表来执行您要查询的应用程序层中的递归或将级别存储为第三列。

In any event, to do what you're asking with an adjacency list you're looking at either recursion in the application layer, or storing the level as a 3rd column.

ETA：如果你的关卡数量不是很高，你可以用自联接：

ETA: if your level count isn't terribly high, you can do it with self joins:

具有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;

这篇关于mysql获得记录深度，计数父记录和祖先记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！