mysql - 在mysql查询中获取子级别

Question

我的表具有如下 id 和 perent:

id   parent
12      0
21      12
34      12
47      21
57      21
66      21
77      34
88      47

我想写查询获取id并返回这个id的级别:

例如

1 => level 1
2 and 3 => level 2
4 => level 3
... and so on.

我尝试了什么:

SELECT cc1.id, count(cc1.id) c FROM community_comments cc1
inner join community_comments cc2 on cc1.id = cc2.parent 
AND cc1.parent = 324 group by cc1.parent

这会返回 324 级别以下的级别，这不是我想要的

Answer 1

我正在使用 PHP 代码解决它

function get_comment_level($comment_id){

        $query = "select parent from community_comments where id = $comment_id";

        $result = $this->conn->db_query($query);

        $level = 1;
        $parent = 0;

        while ($row = $this->conn->db_fetch_object($result)){
            $parent = $row->parent;
        }

        if ($parent != 0) {
            $level = $this->get_comment_level($parent);
            $level++;
        }

        return $level;
    }