在一个SQL查询中获取所有父行 [英] Getting all parent rows in one SQL query

问题描述

我有一个简单的MySQL表，其中包含类别列表，级别由parent_id确定:

I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

我正在尝试进行面包屑跟踪.因此，我有孩子的身份证"，我想让所有可用的父母(重复链，直到我们达到0个家"为止).可以有任意数量的行或子行达到无限的深度.

I'm attempting to make a breadcrumb trail. So i have the 'id' of the child, I want to get all available parents (iterating up the chain until we reach 0 "Home"). There could be any number or child rows going to an unlimited depth.

当前，我正在为每个父级使用SQL调用，这很麻烦. SQL中是否有一种方法可以对一个查询执行全部操作?

Currently I am using an SQL call for each parent, this is messy. Is there a way in SQL to do this all on one query?

推荐答案

改编自 @r := 5行是当前页面的页码.结果如下:

The line @r := 5 is the page number for the current page. The result is as follows:

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'

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