mysql查询获取根父级 [英] mysql query to get the root parent

问题描述

我在表categories中具有以下数据:

I have the following data in table categories:

 id  parent_id    title
 -----------------|-------------------
 1         0      |A
 2         1      |B
 3         2      |C
 4         3      |D
 5         4      |E

现在，我要获取类别ID 4的根类别标题(parent_id 0)，即标题A的类别ID 1.

Now I want to get the root category title(parent_id 0) for category id 4 and that is category id 1 of title A.

如何在单个mysql查询中获得所需的结果?我假设将使用过程查询，但我不知道该怎么做.

How can I achieve the required result in a single mysql query? I assume procedural query will be used but I dont know how to do it.

推荐答案

尝试此查询

它的工作原理是，在实际查询之前，记录将按照派生表的降序排序，因此父代的ID小于子代的ID.

It works on the assumption that the id of parent are less than child as the records are being sorted in descending as a derived table before actual query.

select 
  @parent:=parent_id as prnt, title, id
from
  (select @parent:=8 ) a
join 
  (select * from tbl order by id desc) b
where 
  @parent=id

小提琴

| PRNT | TITLE | ID |
|------|-------|----|
|    7 |     q |  8 |
|    6 |     a |  7 |
|    0 |     d |  6 |

注意最好的方法是使用存储的过程.

Note Best way is to do it is using a stored proc..

这篇关于mysql查询获取根父级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！