如何在同一查询中选择直系子代和祖先

Question

我正在使用 MySQL 中的树结构，该结构使用嵌套集模型表示。

我希望你们中的一些 sql 专家可以帮助我构建 SELECT 查询。

我希望能够使用 LIKE 匹配一组节点。对于匹配的每个节点，我还需要该节点的祖先的逗号分隔列表，以及该节点的直接子节点的逗号分隔列表。

我不太确定从哪里开始 - 如果这样的事情在单个查询中甚至是可能的。 （目前我正在通过循环内的查询来完成此操作。）我希望得到一个可能看起来像这样的结果集......

从字符串“qu”开始并查询我得到的表“Body” ...

Node      | Parent Nodes               | Immediate Children
Quads       Leg, Lower Body, Muslces     Vastus Lateralus, Vastus Medialis, Rectus Femoris
Obliques    Core, Trunk, Muscles         Inner obliques, outer obliques

任何有关如何在不循环查询的情况下完成此操作的建议将不胜感激。

Answer 1

虽然我同意 nickf 的观点，认为这既糟糕又肮脏，但它仍然很有趣，所以这里是：

SELECT     base.left_id, base.ancestors, 
           GROUP_CONCAT(children.left_id) children
FROM       (
            SELECT     base.left_id
            ,          GROUP_CONCAT(ancestors.left_id) ancestors
            FROM       nested_set   base
            LEFT JOIN  nested_set   ancestors
            ON         base.left_id     BETWEEN ancestors.left_id 
                                            AND ancestors.right_id
            WHERE      base.name  LIKE '%criteria%'
            GROUP BY   base.left_id
           ) base                                    
LEFT JOIN  nested_set   children
ON         children.left_id BETWEEN base.left_id 
                                AND base.right_id
LEFT JOIN  nested_set   inbetween
ON         inbetween.left_id BETWEEN base.left_id 
                                AND base.right_id
AND        children.left_id  BETWEEN inbetween.left_id 
                                AND inbetween.right_id     
WHERE      inbetween.left_id IS NULL
GROUP BY   base.left_id

基本上，诀窍是分两步解决它：首先，解决祖先问题，并将祖先压缩到一个列表中，然后，用这个结果为孩子们解决这个问题。

祖先部分相对简单，它是我的解决方案中 from 子句中的子查询。
孩子们就有点难了。它的工作原理是获取所有后代，然后要求基节点和后代之间不存在任何节点，这基本上将后代限制为仅限子节点。

此策略还有其他变体可以解决此问题 - 例如，您可以先处理子项，然后使用 SELECT 列表中的子查询来解决祖先项。

Answer 2

在一个查询中？我不会打扰。 SQL 将是可怕的，甚至可能没有那么有效。将每个位拆分为逻辑上较小的查询：首先找到所有匹配的节点，然后针对每个节点找到您需要的额外信息。

Answer 3

我不是 100% 确定你想要什么，但如果我理解正确，你可以使用规范化的数据库模式和子查询来实现这一点。

例如：

表“节点”
表“node_parents”

“nodes”表将存储所有节点，“node_parents”将映射不同节点之间的关系。

因此，当您选择 LIKE 某个节点时，您可以从 node_parents 中获取其所有父节点和子节点。

您可以使用连接或子查询来获取额外的信息。

Answer 4

这个问题比我在你的其他帖子中预期的要困难得多，但我不同意第一个海报的答案。

我非常有信心通过单个查询就可以实现这一点。

您需要使用 SUBQUERIES 和选择。你有没有看过 mySQL 网站上有关邻接列表模型的非常好的演示。

因为您可以对“节点”进行 LIKE，所以您可以对 SQL 查询使用子查询来获取所有父节点和父节点。
我做这样的事情的一个查询绝对是巨大的！但它奏效了。

看看：
http://mikehillyer.com/articles/managing-hierarchical-data-in -mysql/

这只是一个小代码片段，显示了如何找到直接子级。

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