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

Question

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

sqlmysqltreehierarchical-datanested-sets

6

我正在使用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

任何不需要循环查询就能完成这个任务的建议都将不胜感激。

- Travis

我目前没有时间写完整的答案，但如果您可以访问《SQL Cookbook》这本书，其中关于分层查询的章节涵盖了使用MySQL解决此问题。对于MySQL，您需要知道分支深度才能使用查询，但这不是问题-您知道想要深入多少。解决方案并不是超级简单的，但是有可能实现。 - J. Polfer

@sheepsimulator - 谢谢，我会看看能否找到那本书。 - Travis

羊模拟器，我不同意。如果你知道你需要什么深度，解决这个问题是微不足道的 - 神圣的圣杯是为任意深度解决它。或者说不是神圣的圣杯，但是任意深度是我认为这个问题有趣的地方。 - Roland Bouman

@ Roland - Concur。但 Travis 很幸运，他不必这样做。 - J. Polfer

4个回答

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

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

- Roland Bouman · 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列表中的子查询来解决祖先问题。

- Layke · Answer 2

在你的另一篇帖子中，这个问题比我预期的要难得多，但我不同意第一个回答者的答案。

我相当有信心可以用单个查询实现。

您需要使用子查询和选择。您是否曾经看过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

- nickf · Answer 3

0

一个查询里面吗？我觉得不必这么麻烦。SQL 语句会非常可怕，而且可能并不高效。最好将每个部分拆分成逻辑更小的查询：首先找到所有匹配的节点，然后对于每个节点，再查找所需的额外信息。

- nickf

这是我目前正在做的事情。我首先使用LIKE选择匹配的节点。然后，我循环遍历它们，并在每次迭代中单独查询祖先和子节点。根据初始结果集的大小，可能需要进行大量查询... - Travis

2

我明白这一点，但是怎么样呢？我仍然更喜欢阅读/维护多个执行单个、易于理解的功能的较小查询，而不是一个庞大的查询，只有超级专家才能看懂。 - nickf

好吧，我不是超级大牛。 :) 我只是试图优化我的SQL。我一直认为，一旦你开始循环你的SQL查询，灾难就在眼前了。 - Travis

- Alex Weber · Answer 4

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

例如：

表"nodes" 表"node_parents"

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

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

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