php - MySQL 查询(分层数据)返回错误的节点深度

Question

我有一个 MySQL 查询，该查询应该从数据库表中选择属于某个公司的多个分层位置。只需要使用一张表。

如果表中的所有位置都属于一家公司，则查询工作正常，但如果我添加属于其他公司的任何位置，则计算的深度(使用嵌套集模型计算的层次结构中的级别)返回不正确。最后创建的公司位置仍然返回正确的结果，但所有先前的公司都返回错误的深度。

所以我猜我的查询以某种方式从相关公司以外的公司获取行，因此所有结果都会变得困惑，但是我只是无法弄清楚为什么以及到底在哪里这样做。

我正在使用THIS文章作为我分层数据(嵌套集方法)查询的引用。

这是查询:

//This query will return a result set with all 'locations' that are on and below the hierarchy level of
//the specified location. It will also add a depth field to each row which
//shows how deep each location is in relation to the named starting location.
//Any location name can be supplied, even the root location.
//This query uses three self-joins and a sub-query to determine the depth of each location in relation to the starting
//location.
"   SELECT location.location_id, location.location_name, location.location_company_id, location.location_active, (COUNT(parent.location_name) - (sub_tree.depth + 1))
    AS depth
    FROM locations AS location,
         locations AS parent,
         locations AS sub_parent,
         (
             SELECT location.location_id, (COUNT(parent.location_name) - 1) AS depth
             FROM locations AS location,
                 locations AS parent
                 WHERE location.lft
                 BETWEEN parent.lft
                 AND parent.rgt
                 AND location.location_id = 334
                 AND location.location_company_id = 1001
                 GROUP BY location.location_id
                 ORDER BY location.lft
         )
         AS sub_tree
    WHERE location.lft BETWEEN parent.lft AND parent.rgt
    AND location.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.location_id = sub_tree.location_id
    AND location.location_company_id = 1001
    GROUP BY location.location_id
    ORDER BY location.lft;
"

当“位置”表中的数据如下(仅限一个公司位置)时，此查询效果很好:

location_id location_name   location_company_id lft rgt location_active
    334   Company 1            1001              1   6        1
    335   Comp1 Loc1           1001              4   5        1
    336   Comp1 Loc2           1001              2   3        1

在这种情况下，深度计算正确。

但是，如果我在表中添加更多具有某些位置的公司，则会出现问题。顺便说一句，表中位置越多的公司，深度误差就越大。第一家公司的深度最不准确，第二家公司的深度稍微不准确，最后一家公司的深度正确。这是包含三个公司地点的表格:

location_id location_name   location_company_id   lft    rgt   location_active
     334      Company 1             1001          1       14       1
     335      Comp1 Loc1            1001          12      13       1
     336      Comp1 Loc2            1001          10      11       1
     337      Company 2             1002          1       10       1
     338      Comp2 Loc1            1002          8       9        1
     339      Comp2 Loc2            1002          6       7        1
     340      Company 3             1003          1       6        1
     341      Comp3 Loc1            1003          4       5        1
     342      Comp3 Loc2            1003          2       3        1

我无法弄清楚是表中的数据不正确(lft 和 rgt)还是查询本身错误以及如何修复它。

任何帮助、提示或建议，我们将不胜感激。

Answer 1

您错误地更新了 lft 和 rgt 值。

Comp1 将 lft 和 rgt 设置为 1-14，而 Comp2 将其设置为 1-10这意味着 Comp1 具有从 1-14 的所有位置，而 comp2 具有从 1-10 的所有位置

Comp1 可能应为 9-14，Comp2 可能应为 6-10

除此之外，现在您在 mySQL 中创建的二叉树的性能非常差。最好使用支持WITH语句的数据库(例如DB2)