嵌套集查询以检索每个节点的所有祖先 [英] Nested Set Query to retrieve all ancestors of each node

问题描述

我有一个 MySQL 查询，我认为它可以很好地检索每个节点的所有祖先，从顶部节点开始，一直到其直接节点.但是，当我向嵌套集添加第 5 级时，它坏了.

以下是示例表、查询和 SQL Fiddles:

四级嵌套集:

创建表树(title varchar(20) PRIMARY KEY,`tree` int，`左` int，`正确` int);插入树价值观(食物", 1, 1, 18),('水果', 1, 2, 11),('红色', 1, 3, 6),('樱桃', 1, 4, 5),('黄色', 1, 7, 10),('香蕉', 1, 8, 9),('肉', 1, 12, 17),('牛肉', 1, 13, 14),('猪肉', 1, 15, 16);

查询:

SELECT t0.title 节点,(SELECT GROUP_CONCAT(t2.title)从树 t2WHERE t2.leftt0.rightORDER BY t2.left) 祖先从树 t0GROUP BY t0.title;

节点Banana的返回结果是Food,Fruit,Yellow - Perfect.你可以在这里看到这个SQL Fiddle - 4 Levels

当我在下面的 5 级表上运行相同的查询时，第 5 级节点以错误的顺序返回:

创建表树(title varchar(20) PRIMARY KEY,`tree` int，`左` int，`正确` int);插入树价值观(食物", 1, 1, 24),('水果', 1, 2, 13),('红色', 1, 3, 8),('樱桃', 1, 4, 7),('Cherry_pie', 1, 5, 6),('黄色', 1, 9, 12),('香蕉', 1, 10, 11),('肉', 1, 14, 23),('牛肉', 1, 15, 16),('猪肉', 1, 17, 22),('培根', 1, 18, 21),('培根_三明治', 1, 19, 20);

Bacon_Sandwich 返回的结果是 Bacon,Food,Meat,Pork 这不是正确的顺序，应该是 Food,Meat,Pork,Bacon - 你可以在这里看到这个 SQL Fiddle - 5 Levels

我不确定发生了什么，因为我不太了解子查询.任何人都可以对此有所了解吗?

调查后

哇！！写出所有这些内容并阅读有关使用 GROUP_CONCAT 进行订购的信息似乎给了我一些启发.

将 ORDER BY 添加到实际的 GROUP_CONCAT 函数并从子查询的末尾删除解决了该问题.我现在收到节点 Bacon_Sandwich

的 Food,Meat,Pork,Bacon

SELECT t0.title 节点,(SELECT GROUP_CONCAT(t2.title ORDER BY t2.left)从树 t2WHERE t2.leftt0.right) 祖先从树 t0GROUP BY t0.title;

我仍然不知道为什么.在子查询末尾使用 ORDER BY 可用于 4 个级别，但不适用于 5 个级别?！?！

如果有人能解释问题是什么以及为什么移动 ORDER BY 可以修复它，我将不胜感激.

解决方案

首先重要的是要了解您有一个 隐式GROUP BY

<块引用>

如果在不包含 GROUP BY 子句的语句中使用 group 函数，则相当于对所有行进行分组.

为了使这一点更容易理解，我将省略子查询并将问题简化为香蕉.Banana 是集合 [10, 11].正确排序的祖先是那些:

SELECT "banana" 作为节点, GROUP_CONCAT(title ORDER by `left`)FROM Tree WHERE `left` <10 AND `右` >11GROUP BY 节点；

ORDER BY 必须在 GROUP_CONCAT() 中，因为您希望聚合函数进行排序.ORDER BY 外部按聚合结果排序(即 GROUP_CONCAT() 的结果).它一直工作到第 4 级的事实只是运气.ORDER BY 对聚合函数没有影响.无论是否使用 ORDER BY，您都会得到相同的结果:

SELECT GROUP_CONCAT(标题)FROM Tree WHERE `left` <10 AND `右` >11/* 按`左`排序 */

<小时>

这可能有助于理解什么SELECT GROUP_CONCAT(title ORDER BY left) FROM Tree WHERE ... ORDER BY left 做:

1. 获取一个选择(WHERE)，它会以未定义的顺序生成三行:

   <前>(食物")(黄色的")(水果")

2. 将结果聚合为一行(隐式GROUP BY)以便能够使用聚合函数:

   <前>((食物"，黄色"，水果"))

3. 在其上触发聚合函数 (GROUP_CONCAT(title, ORDER BY link)).IE.按链接排序，然后连接:

   <前>(食物，水果，黄色")

4. 现在终于对结果进行排序(ORDER BY).由于只有一行，排序没有任何改变.

   <前>(食物，水果，黄色")

I have a MySQL query that I thought was working fine to retrieve all the ancestors of each node, starting from the top node, down to its immediate node. However when I added a 5th level to the nested set, it broke.

Below are example tables, queries and SQL Fiddles:

Four Level Nested Set:

CREATE TABLE Tree
(title varchar(20) PRIMARY KEY,
 `tree` int,
 `left` int,
 `right` int);

INSERT Tree
VALUES
("Food", 1, 1, 18),
('Fruit', 1, 2, 11),
('Red', 1, 3, 6),
('Cherry', 1, 4, 5),
('Yellow', 1, 7, 10),
('Banana', 1, 8, 9),
('Meat', 1, 12, 17),
('Beef', 1, 13, 14),
('Pork', 1, 15, 16);

The Query:

SELECT t0.title node
      ,(SELECT GROUP_CONCAT(t2.title)
                    FROM Tree t2
                    WHERE t2.left<t0.left AND t2.right>t0.right
                    ORDER BY t2.left) ancestors
FROM Tree t0
GROUP BY t0.title;

The returned result for node Banana is Food,Fruit,Yellow - Perfect. You can see this here SQL Fiddle - 4 Levels

When I run the same query on the 5 level table below, the 5th level nodes come back in the wrong order:

CREATE TABLE Tree
(title varchar(20) PRIMARY KEY,
 `tree` int,
 `left` int,
 `right` int);

INSERT Tree
VALUES
("Food", 1, 1, 24),
('Fruit', 1, 2, 13),
('Red', 1, 3, 8),
('Cherry', 1, 4, 7),
('Cherry_pie', 1, 5, 6),
('Yellow', 1, 9, 12),
('Banana', 1, 10, 11),
('Meat', 1, 14, 23),
('Beef', 1, 15, 16),
('Pork', 1, 17, 22),
('Bacon', 1, 18, 21),
('Bacon_Sandwich', 1, 19, 20);

The returned result for Bacon_Sandwich is Bacon,Food,Meat,Pork which is not the right order, it should be Food,Meat,Pork,Bacon - You can see this here SQL Fiddle - 5 Levels

I am not sure what is happening because I don't really understand subqueries well enough. Can anyone shed any light on this?

EDIT AFTER INVESTIGATION:

Woah!! Looks like writing all this out and reading up about ordering with GROUP_CONCAT gave me some inspiration.

Adding ORDER BY to the actual GROUP_CONCAT function and removing from the end of the subquery solved the issue. I now receive Food,Meat,Pork,Bacon for the node Bacon_Sandwich

SELECT t0.title node
      ,(SELECT GROUP_CONCAT(t2.title ORDER BY t2.left)
                    FROM Tree t2
                    WHERE t2.left<t0.left AND t2.right>t0.right
                    ) ancestors
FROM Tree t0
GROUP BY t0.title;

I still have no idea why though. Having ORDER BY at the end of the subquery works for 4 levels but not for 5?!?!

If someone could explain what the issue is and why moving the ORDER BY fixes it, I'd be most grateful.

解决方案

First it's important to understand that you have an implicit GROUP BY

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

To make the point more understandable I'll leave out subqueries and reduce the problem to the banana. Banana is the set [10, 11]. The correct sorted ancestors are those:

SELECT "banana" as node, GROUP_CONCAT(title ORDER by `left`)
  FROM Tree WHERE `left` < 10 AND `right` > 11
  GROUP BY node;

The ORDER BY must be in GROUP_CONCAT() as you want the aggregation function to sort. ORDER BY outside sorts by the aggregated results (i.e. the result of GROUP_CONCAT()). The fact that it worked until level 4 is just luck. ORDER BY has no effect on an aggregate function. You would get the same results with or without the ORDER BY:

SELECT GROUP_CONCAT(title)
  FROM Tree WHERE `left` < 10 AND `right` > 11
  /* ORDER BY `left` */

---

It might help to understand what SELECT GROUP_CONCAT(title ORDER BY left) FROM Tree WHERE … ORDER BY left does:

1. Get a selection (WHERE) which results in three rows in an undefined order:

   ("Food")
   ("Yellow")
   ("Fruit")
   

2. Aggregate the result into one row (implicit GROUP BY) in order to be able to use an aggregate function:

   (("Food","Yellow", "Fruit"))
   

3. Fire the aggregate function (GROUP_CONCAT(title, ORDER BY link)) on it. I.e. order by link and then concatenate:

   ("Food,Fruit,Yellow")
   

4. And now finally it sorts that result (ORDER BY). As it's only one row, sorting changes nothing.

   ("Food,Fruit,Yellow")
   

这篇关于嵌套集查询以检索每个节点的所有祖先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！