mysql查询祖类,mysql获得记录深度，计数父记录和祖先记录

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.

Take this data for example...

post_id parent_post_id

------- --------------

1 null

2 1

3 1

4 2

5 4

The data represents this hierarchy...

1

|_ 2

| |_ 4

| |_ 5

|_ 3

The result of the query should be...

post_id depth

------- -----

1 0

2 1

3 1

4 2

5 3

Thanks in advance!

解决方案

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: if your level count isn't terribly high, you can do it with self joins:

e.g. nodes with 2 ancestors:

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;