mysql - 修改后的预购树 : selecting top elements of specific type

Question

我使用修改后的预序树将应用程序中的 GEO 位置存储在单个表 LOC_TABLE 中。子树的一个例​​子，例如希腊看起来像这样:

+-------+---------------+-----+-----+------+
| ID    | NAME          | LFT | RGT | TYPE |
+-------+---------------+-----+-----+------+
|    10 | Greece        | 100 | 200 |   3  |
|    20 | Crete Isl.    | 120 | 140 |   4  |
|    25 | Crete-Vamos   | 121 | 122 |   4  |
|    26 | Crete-Rethymno| 123 | 124 |   4  |
 ....
+-------+---------------+-----+-----+------+

TYPE 列用于存储位置类型(3 - 国家/地区，4 - 城市)。正如您所看到的，克里特岛存储为城市，其中包含其他城市(例如 Vamos 和 Rethymno)作为其子城市。

我需要执行两种类型的查询:

1) 获取特定父项下特定类型的所有位置。

2) 获取特定父级下特定类型的所有顶级位置:对于提供的位置示例，在查询希腊境内的城市时，仅应返回 Crete Isl.，因为 Crete Isl. 没有城市类型的父级，而城市 Vamos 和 Rethymno 具有城市类型的父级 - Crete Isl。

在每种情况下执行最快的查询是什么？

对于第一种情况，我考虑使用两个查询(第一个，获取希腊的 LFT 和 RGT，第二个获取类型 = 4 的所有位置，这些位置具有适当的 LFT 和 RGT)或使用某种连接来获取所有位置一步之遥。哪种方法最好？

对于第二种情况我目前没有任何合适的想法。我尝试了简单的子选择:

select loc.* from LOC_TABLE loc 
where 4 not in 
(select TYPE from LOC_TABLE p 
    where p.lft < loc.lft AND p.rgt > loc.rgt) 
AND loc.LFT > 100 AND loc.RGT < 200;

但是太长了。

我不介意添加更多列并用一些值填充它们，这将有助于加快这两种类型的查询。但我需要快速获取数据。

谢谢。

Answer 1

对于属于城市以及具有由 :id 指定的 ID 的特定记录的后代的所有记录，请使用

SELECT descendant.ID, descendant.NAME, descendant.TYPE 
FROM LOC_TABLE location
INNER JOIN LOC_TABLE descendant
    ON descendant.LFT > location.LFT AND descendant.RGT < location.RGT
WHERE location.ID = :id AND TYPE = 4

对于所有属于城市且具有由 :id 给出的 ID 的记录的子记录，请使用

SELECT child.ID, child.NAME, child.TYPE 
FROM LOC_TABLE location
INNER JOIN LOC_TABLE child
    ON child.LFT > location.LFT AND child.RGT < location.RGT
LEFT OUTER JOIN LOC_TABLE intermediate
    ON intermediate.LFT < child.LFT
    AND intermediate.RGT > child.RGT
    AND intermediate.LFT > location.LFT
    AND intermediate.RGT < location.RGT
WHERE location.ID = :id
AND child.TYPE = 4
AND intermediate.ID IS NULL

LEFT OUTER JOIN 与条件 intermediate.ID IS NULL 一起消除了那些 child 记录，其后代为 location 是 child 的祖先。