blocks|key|102589|text|leftclickben+answer对我来说很有效，但我想要一条从给定节点到树的根的路径，而这些路径似乎是沿着树的另一个方向向下走的。因此，为了清楚起见，我不得不翻转一些字段并重新命名，这对我来说很有效，以防其他人也想要这样--|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|102590|item+%7C+parent
-------------
1++++%7C+null
2++++%7C+1
3++++%7C+1
4++++%7C+2
5++++%7C+4
6++++%7C+3|code-block|syntax|javascript|102591|和|102592|select+t.item_id+as+item,+@pv:=t.parent+as+parent
from+(select+*+from+item_tree+order+by+item_id+desc)+t
join
(select+@pv:=6)tmp
where+t.item_id=@pv;|102593|提供：|102594|item+%7C+parent
-------------
6++++%7C+3
3++++%7C+1
1++++%7C+null|102595|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/24901882/3163120^0|0|J|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|16|8|@]|9|@]|C|$G|H]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

<a href="https://stackoverflow.com/a/24901882/3163120">leftclickben answer</a> worked for me, but I wanted a path from a given node back up the tree to the root, and these seemed to be going the other way, down the tree. So, I had to flip some of the fields around and renamed for clarity, and this works for me, in case this is what anyone else wants too--

<pre><code>item | parent
-------------
1 | null
2 | 1
3 | 1
4 | 2
5 | 4
6 | 3
</code></pre>

and

<pre><code>select t.item_id as item, @pv:=t.parent as parent
from (select * from item_tree order by item_id desc) t
join
(select @pv:=6)tmp
where t.item_id=@pv;
</code></pre>

gives:

<pre><code>item | parent
-------------
6 | 3
3 | 1
1 | null
</code></pre>

blocks|key|319696|text|如果您希望能够进行SELECT而不存在父id必须小于子id的问题，则可以使用函数。它还支持多个孩子(正如树应该做的那样)，并且树可以有多个头。如果数据中存在循环，它还会确保中断。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319697|我想使用dynamic+SQL来传递表名/列名，但MySQL中的函数不支持这一点。|319698|DELIMITER+$$

CREATE+FUNCTION+`isSubElement`(pParentId+INT,+pId+INT)+RETURNS+int(11)
DETERMINISTIC++++
READS+SQL+DATA
BEGIN
DECLARE+isChild,curId,curParent,lastParent+int;
SET+isChild+=+0;
SET+curId+=+pId;
SET+curParent+=+-1;
SET+lastParent+=+-2;

WHILE+lastParent+<>+curParent+AND+curParent+<>+0+AND+curId+<>+-1+AND+curParent+<>+pId+AND+isChild+=+0+DO
++++SET+lastParent+=+curParent;
++++SELECT+ParentId+from+`test`+where+id=curId+limit+1+into+curParent;

++++IF+curParent+=+pParentId+THEN
++++++++SET+isChild+=+1;
++++END+IF;
++++SET+curId+=+curParent;
END+WHILE;

RETURN+isChild;
END$$|code-block|syntax|javascript|319699|在这里，表test必须修改为真实的表名，并且列(ParentId，Id)可能需要根据您的真实名称进行调整。|offset|length|style|CODE|319700|用法：|319701|SET+@wantedSubTreeId+=+3;
SELECT+*+FROM+test+WHERE+isSubElement(@wantedSubTreeId,id)+=+1+OR+ID+=+@wantedSubTreeId;|319702|结果：|319703|3+++7+++k
5+++3+++d
9+++3+++f
1+++5+++a|319704|用于创建测试的SQL：|319705|CREATE+TABLE+IF+NOT+EXISTS+`test`+(
++`Id`+int(11)+NOT+NULL,
++`ParentId`+int(11)+DEFAULT+NULL,
++`Name`+varchar(300)+NOT+NULL,
++PRIMARY+KEY+(`Id`)
)+ENGINE=InnoDB++DEFAULT+CHARSET=latin1;

insert+into+test+(id,+parentid,+name)+values(3,7,'k');
insert+into+test+(id,+parentid,+name)+values(5,3,'d');
insert+into+test+(id,+parentid,+name)+values(9,3,'f');
insert+into+test+(id,+parentid,+name)+values(1,5,'a');
insert+into+test+(id,+parentid,+name)+values(6,2,'o');
insert+into+test+(id,+parentid,+name)+values(2,8,'c');|319706|编辑:这是一个你可以自己测试的fiddle。它迫使我使用预定义的分隔符更改分隔符，但它是有效的。|319707|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/783867/1/1^0|0|0|0|5|4|0|0|0|0|0|0|0|F|6|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1D|8|@$K|1E|L|1F|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1H|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|1J|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|1L|8|@]|9|@]|A|$G|H]]|$1|10|3|11|5|6|7|1M|8|@]|9|@$K|1N|L|1O|1|1P]]|A|$]]|$1|12|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

If you want to be able to have a SELECT without problems of the parent id having to be lower than child id, a function could be used. It supports also multiple children (as a tree should do) and the tree can have multiple heads. It also ensure to break if a loop exists in the data.

I wanted to use dynamic SQL to be able to pass the table/columns names, but functions in MySQL don't support this.

<pre><code>DELIMITER $$

CREATE FUNCTION `isSubElement`(pParentId INT, pId INT) RETURNS int(11)
DETERMINISTIC 
READS SQL DATA
BEGIN
DECLARE isChild,curId,curParent,lastParent int;
SET isChild = 0;
SET curId = pId;
SET curParent = -1;
SET lastParent = -2;

WHILE lastParent &lt;&gt; curParent AND curParent &lt;&gt; 0 AND curId &lt;&gt; -1 AND curParent &lt;&gt; pId AND isChild = 0 DO
 SET lastParent = curParent;
 SELECT ParentId from `test` where id=curId limit 1 into curParent;

 IF curParent = pParentId THEN
 SET isChild = 1;
 END IF;
 SET curId = curParent;
END WHILE;

RETURN isChild;
END$$
</code></pre>

Here, the table <code>test</code> has to be modified to the real table name and the columns (ParentId,Id) may have to be adjusted for your real names.

Usage :

<pre><code>SET @wantedSubTreeId = 3;
SELECT * FROM test WHERE isSubElement(@wantedSubTreeId,id) = 1 OR ID = @wantedSubTreeId;
</code></pre>

Result :

<pre><code>3 7 k
5 3 d
9 3 f
1 5 a
</code></pre>

SQL for test creation :

<pre><code>CREATE TABLE IF NOT EXISTS `test` (
 `Id` int(11) NOT NULL,
 `ParentId` int(11) DEFAULT NULL,
 `Name` varchar(300) NOT NULL,
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into test (id, parentid, name) values(3,7,'k');
insert into test (id, parentid, name) values(5,3,'d');
insert into test (id, parentid, name) values(9,3,'f');
insert into test (id, parentid, name) values(1,5,'a');
insert into test (id, parentid, name) values(6,2,'o');
insert into test (id, parentid, name) values(2,8,'c');
</code></pre>

EDIT : Here is a <a href="http://sqlfiddle.com/#!9/783867/1/1" rel="noreferrer">fiddle</a> to test it yourself. It forced me to change the delimiter using the predefined one, but it works.

blocks|key|102674|text|在主DJon基础上构建|type|unstyled|depth|inlineStyleRanges|entityRanges|data|102675|这是一个简化的函数，它提供了返回深度的附加工具(如果你想使用逻辑来包含父任务或在特定深度进行搜索)|102676|DELIMITER+$$
FUNCTION+`childDepth`(pParentId+INT,+pId+INT)+RETURNS+int(11)
++++READS+SQL+DATA
++++DETERMINISTIC
BEGIN
DECLARE+depth,curId+int;
SET+depth+=+0;
SET+curId+=+pId;

WHILE+curId+IS+not+null+AND+curId+<>+pParentId+DO
++++SELECT+ParentId+from+test+where+id=curId+limit+1+into+curId;
++++SET+depth+=+depth+%2B+1;
END+WHILE;

IF+curId+IS+NULL+THEN
++++set+depth+=+-1;
END+IF;

RETURN+depth;
END$$|code-block|syntax|javascript|102677|用法：|102678|select+*+from+test+where+childDepth(1,+id)+<>+-1;|102679|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Building off of Master DJon

Here is simplified function which provides the added utility of returning depth (in case you want to use logic to include the parent task or search at a specific depth)

<pre><code>DELIMITER $$
FUNCTION `childDepth`(pParentId INT, pId INT) RETURNS int(11)
 READS SQL DATA
 DETERMINISTIC
BEGIN
DECLARE depth,curId int;
SET depth = 0;
SET curId = pId;

WHILE curId IS not null AND curId &lt;&gt; pParentId DO
 SELECT ParentId from test where id=curId limit 1 into curId;
 SET depth = depth + 1;
END WHILE;

IF curId IS NULL THEN
 set depth = -1;
END IF;

RETURN depth;
END$$
</code></pre>

Usage:

<pre><code>select * from test where childDepth(1, id) &lt;&gt; -1;
</code></pre>

I got a following table:

<pre><code>col1 | col2 | col3
-----+------+-------
1 | a | 5
5 | d | 3
3 | k | 7
6 | o | 2
2 | 0 | 8
</code></pre>

If a user searches for "1", the program will look at the <code>col1</code> that has "1" then it will get a value in <code>col3</code> "5", then the program will continue to search for "5" in <code>col1</code> and it will get "3" in <code>col3</code>, and so on. So it will print out:

<pre><code>1 | a | 5
5 | d | 3
3 | k | 7
</code></pre>

If a user search for "6", it will print out:

<pre><code>6 | o | 2
2 | 0 | 8
</code></pre>

How to build a <code>SELECT</code> query to do that?

How to do the Recursive SELECT query in MySQL?

MySQL

我有一个表格：col1 | col2 | col3-----+------+-------1    | a    | 55    | d    | 33    | k    | 76    | o    | 22    | 0    | 8如果用户搜索"1"，程序将查看具有"1“的col1，然后将得到col3中的值"5...

问如何在MySQL中进行递归选择查询？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中进行递归选择查询？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中进行递归选择查询？
EN