MySQL选择同一表中由ID链接的所有行 [英] MySQL Select All Rows Linked By ID In Same Table

问题描述

我有一个包含字段的表格:

I have a table with the fields:

Parent 引用同一个表中的主键 ID(不是外键).

Parent references the primary key ID in the same table (not foreign key).

+----+--------+------------+
| ID | Parent |   Title    |
+----+--------+------------+
|  1 |      0 | Wallpapers |
|  2 |      1 | Nature     |
|  3 |      2 | Trees      |
|  4 |      3 | Leaves     |
+----+--------+------------+

我正在尝试选择一行及其所有父项.例如，如果我选择Leaves"，我还想获得Trees"、Nature"和Wallpaper"，因为这是层次结构的父路径.这在一个查询中是可能的吗?目前，我在 ColdFusion 中使用循环来获取每次迭代的下一个父级，但效率不够高.

I'm trying to select a row and all its parents. For example if I select "Leaves" I want to get "Trees", "Nature", and "Wallpaper" as well since that is the parent path up the hierarchy. Is this possible in one query? At the moment I'm using a loop in ColdFusion to get the next parent each iteration but it's not efficient enough.

推荐答案

你必须使用自连接来实现:-

You have to use a self join to achieve this:-

 SELECT T1.Title AS CHILD, T2.Title AS PARENT
 FROM YOUR_TABLE T1, YOUR_TABLE T2
 WHERE T1.PARENT = T2.ID
 AND T1.Title = 'Leaves'

这篇关于MySQL选择同一表中由ID链接的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！