mysql，找到父节点 [英] mysql, find parent node

问题描述

我有一个标准的分层表ID/PID，如果某个孩子属于某个ID，我需要查找(布尔值).有没有办法在mysql内做到这一点?

I have a standard hierarchical table ID/PID and I need to find (boolean) if some child belongs to some ID. Is there a way to do this within mysql?

ID   PID
------------
1     0          <- root
...   ...
...   ... 
...   ...
7     1
...   ...
16    1
...   ...
4     0
...   ...
22    16
25    16

是否有办法知道子节点22是否属于节点1?我之所以问是因为我想实现一些删除过程，其中节点1是我的回收站.所有属于1的删除将被永久删除，所有其他删除将被替换为PID，因此暂时属于1.

Is there a way to know if child node 22 belongs to node 1 ? I'm asking because I want to implement some Delete procedure, where node 1 is my Recycle. All deletes belongs to 1 will be deleted permanently and all other delete will have PID replaced, so temporarily belongs now to 1.

非常感谢

推荐答案

基于 MySQL中的分层查询:

SET @x := 22;
SET @y := 0;

select 'yes' as x_comes_from_y 
from dual
where @y in
(
 SELECT  @id :=
        (
        SELECT  pid
        FROM    h
        WHERE   id = @id
        ) AS nodes
 FROM    (
        SELECT  @id := @x
        ) vars
 STRAIGHT_JOIN
        h
 WHERE   @id IS NOT NULL
);

对其进行测试.

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