检索每个节点的所有祖先的嵌套集查询

Question

检索每个节点的所有祖先的嵌套集查询

8

我有一个MySQL查询，我认为它可以很好地检索每个节点的所有祖先，从顶部节点开始，一直到其直接节点。但是当我将第五级添加到嵌套集合中时，它就出现了故障。

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

四级嵌套集合：

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);

查询：

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;

节点Banana的返回结果为食物、水果、黄色 - 完美。您可以在此处查看：SQL Fiddle - 4 Levels 当我在下面的5级表上运行相同的查询时，第5级节点的顺序错误：

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);

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 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;

我仍然不知道为什么。在子查询的末尾使用ORDER BY可以解决4级问题，但不能解决5级问题？！？

如果有人能解释问题以及为什么移动ORDER BY可以解决它，我将非常感激。

- superphonic

2个回答

4

你可以使用 JOIN 或 SUB-QUERY 来获取结果。 使用 JOIN：

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

请查看SQL FIDDLE DEMO

使用子查询：

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;

请查看此SQL FIDDLE DEMO。

输出结果

|           NODE |             ANCESTORS |
|----------------|-----------------------|
|          Bacon |        Food,Meat,Pork |
| Bacon_Sandwich |  Food,Meat,Pork,Bacon |
|         Banana |     Food,Fruit,Yellow |
|           Beef |             Food,Meat |
|         Cherry |        Food,Fruit,Red |
|     Cherry_pie | Food,Fruit,Red,Cherry |
|           Food |                (null) |
|          Fruit |                  Food |
|           Meat |                  Food |
|           Pork |             Food,Meat |
|            Red |            Food,Fruit |
|         Yellow |            Food,Fruit |

在您的子查询中，您在WHERE子句之后使用了ORDER BY，这不会影响输出。默认情况下，GROUP_CONCAT()函数将按列值升序排列输出字符串。它不会考虑您明确的ORDER BY子句。

如果您检查第一个查询的输出，它将按title列的升序返回数据。因此节点Banana的返回结果为Food,Fruit,Yellow。

但是，在第二个结果中，Bacon_Sandwich的结果是Bacon,Food,Meat,Pork，因为按升序排列Bacon先于Food。

如果您想基于left列对结果进行排序，则必须在GROUP_CONCAT()函数内部指定ORDER BY，如上所示。请检查我的两个查询。

我建议您使用JOIN而不是SUB-QUERY来提高性能。

- Saharsh Shah

我确定我留下了一条评论，指向手册。所以再次强调：GROUP_CONCAT()默认情况下不排序。 - Markus Malkusch

感谢您提供的额外解释。 - superphonic

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Markus Malkusch · Accepted Answer

首先，重要的是要理解您拥有一个隐式 GROUP BY。

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

为了更好地说明这一点，我将省略子查询并将问题简化为香蕉。香蕉是集合 [10, 11]。正确排序的祖先是:

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

"

ORDER BY 必须在 GROUP_CONCAT() 中，因为您希望聚合函数进行排序。在外部使用 ORDER BY 会按聚合结果排序（即 GROUP_CONCAT() 的结果）。仅在第四级之前有效是运气。 ORDER BY 对聚合函数没有影响。无论是否使用 ORDER BY，您都将获得相同的结果：

"

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

了解一下SELECT GROUP_CONCAT(title ORDER BY left) FROM Tree WHERE … ORDER BY left的作用可能会有所帮助：

获取一个选择（WHERE），结果为三行，顺序未定义：
```
("Food")
("Yellow")
("Fruit")
```
将结果聚合成一行（隐式GROUP BY），以便使用聚合函数：
```
(("Food","Yellow", "Fruit"))
```
在其上触发聚合函数(GROUP_CONCAT(title, ORDER BY link))。即按链接排序，然后连接：
```
("Food,Fruit,Yellow")
```
现在最后对结果进行排序(ORDER BY)。由于只有一行，排序不会改变任何东西。
```
("Food,Fruit,Yellow")
```