mysql查询获取根父级

本文介绍了mysql查询获取根父级的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在表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..