使用MySQL变量选择分层数据 [英] Selecting hierarchical data using MySQL variable

问题描述

我有一个表，用于存储目录信息(例如在文件系统中).每个目录(命名节点)都有一个nodeid和parentid.我想尽可能有效地检索完整路径.为此，我试图使以下查询正常工作，但是这些查询仅返回预期的第一行(实际上是最里面的目录).任何人都可以帮助获得完整的结果集，以便也选择给定目录/节点的所有层次结构父级.

I have a table where I store information of directories (like in a filesystem). Each directory (named node) has a nodeid and a parentid. I want to retrieve a full path as efficiently as possible. For this, I am trying to make the following queries work, but these return just the first expected row (that is in fact the inner most directory). Can anyone help to get the full result set so that all hierarchical parents of given directory/node are also selected.

我目前正在尝试的查询:

The Queries I am currently experimenting with:

SET @var:= 8;
SELECT * , @var:=parentid FROM `app_nodes` WHERE nodeid IN ( @var )

替代:

SET @var:= 8;
SELECT * , @var:=parentid FROM `app_nodes` WHERE nodeid=@var

替代:

SET @var:= 8;
SELECT * FROM `app_nodes` WHERE nodeid=@var AND (@var:=parentid)
#the parentid of the first parent is '0', so I expect it to end there.

样本数据:

nodeid      parentid
1           0
2           0
3           0
4           0
5           3
6           5
7           0
8           6
9           0
10          0
11          5
12          6
13          11
14          11

所需的输出:

nodeid      parentid
3           0
5           3
6           5
8           6

任何人都可以帮忙吗?

推荐答案

您已经错过了订购数据的需要.请尝试以下操作: SQL小提琴

You've missed the need to order your data. Try the following: SQL Fiddle

select t.nodeid, @pv := t.parentid parentid
from (select * from table1 order by nodeid desc) t
join (select @pv := 8) tmp
where t.nodeid = @pv

输出:

| NODEID | PARENTID |
|--------|----------|
|      8 |        6 |
|      6 |        5 |
|      5 |        3 |
|      3 |        0 |

这篇关于使用MySQL变量选择分层数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！