mysql - SQL:获取嵌套集中节点的直接下属

Question

我正在使用this article作为使用嵌套集模型创建数据库树结构的指南。但是，我无法让它工作:当我执行以下查询时:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

我收到此错误:

#1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and
contains nonaggregated column 'test.node.lft' which is not
functionally dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by

该文章的评论中有人建议按 ID 而不是名称进行分组，因此我将 GROUP BY node.name 替换为 GROUP BY node.category_id 并且有效.

但是，文章中的其他一些查询仍然不起作用。我想选择一个节点的直接子节点，例如:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

但我无法让它工作，因为我不知道它是如何工作的(也不知道从哪里开始)。有人可以解释一下如何理解这些查询吗？也许是一步一步的？

我正在使用 MySQL Ver 14.14 Distrib 5.7.17。

非常感谢!
彼得

Answer 1

您好，我正在使用相同的查询，它对我来说工作正常。

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
            SELECT node.name, (COUNT(parent.name) - 1) AS depth
            FROM nested_category AS node,
                    nested_category AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
                    AND node.id= 36
            GROUP BY node.name
            ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft;

使用node.id代替node.name，并将HAVING深度<= 1更改为= 1，因为我不想显示头节点。