注意：这个问题已经更新，以反映我们目前正在使用 MySQL，这样做后，我想看看如果我们切换到支持 CTE 的数据库会容易得多。
我有一个带有主键id和外键的自引用表parent_id。
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| notes | text | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
给定 a name，我如何查询顶级父级？
给定 a name，我如何查询所有id与 的记录相关联的 's name = 'foo'？
背景：我不是 dba，但我打算请 dba 实现这种类型的层次结构，并想测试一些查询。Kattge et al 2011描述了这样做的动机。
以下是表中 id 之间关系的示例：
-- -----------------------------------------------------
-- Create a new database called 'testdb'
-- -----------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `testdb` ;
-- -----------------------------------------------------
-- Table `testdb`.`observations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testdb`.`observations` (
`id` INT NOT NULL ,
`parent_id` INT NULL ,
`name` VARCHAR(45) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Add Example Data Set
-- -----------------------------------------------------
INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),
(5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);
你肯定必须通过 MySQL 存储过程语言编写脚本
这是一个存储函数
GetParentIDByID，用于检索给定要搜索的 ID 的 ParentID这是一个存储函数
GetAncestry，用于检索从第一代开始的父 ID 列表，所有的层次结构都给定一个 ID 开始：这是生成示例数据的内容：
这是它生成的内容：
以下是函数为每个值生成的内容：
故事寓意：递归数据检索必须在 MySQL 中编写脚本
更新 2011-10-24 17:17 EDT
这是 GetAncestry 的反面。我称之为GetFamilyTree。
这是算法：
我相信从我在大学的数据结构和算法课程中，这被称为前序/前缀树遍历。
这是代码：
这是每行产生的内容
只要没有循环路径，该算法就可以干净地工作。如果存在任何循环路径，则必须在表中添加“已访问”列。
添加访问列后，以下是阻止循环关系的算法：
更新 2011-10-24 17:37 EDT
我创建了一个名为观察的新表并填充了您的示例数据。我将存储过程更改为使用观察而不是 pctable。这是您的输出：
更新 2011-10-24 18:22 EDT
我更改了 GetAncestry 的代码。
WHILE ch > 1应该有WHILE ch > 0现在就试试 ！！！
获取指定节点的所有父节点：
要获取根节点，您可以例如
ORDER BY level取第一行获取指定节点的所有子节点：
（注意语句递归部分中连接的交换条件）
据我所知，以下 DBMS 支持递归 CTE：
编辑
根据您的示例数据，以下内容将从表中检索所有子树，包括每个节点的完整路径作为附加列：
输出将是这样的：
当给定的 id 大于 4 个整数时，Rolando 的答案中的 GetFamilyTree 函数不起作用，因为 FORMAT MySQL 函数为千位分隔符添加了逗号。我已经修改了存储函数 GetFamilyTree 以使用大整数 id，如下所示：
在 if else 循环中定义的 front_id。