如何使用闭包表查询层次结构中的叶节点？

Question

我有一些层次数据(代表乐器和演奏技术)，其中层次结构中的每一叶都是一个音频文件。

叶节的筑巢深度会有所不同，因为有些演奏技术会有亚发音.

叶节点的示例路径可以是

/philharmonia/trumpet/mute/muted_trumpet1.wav
/philharmonia/trumpet/legato/attack/legato_trumpet1.wav
/ircam/violin/pizz/violin_pizz1.wav

我使用主表和闭包表存储数据：

sqlite> PRAGMA table_info(as_node);  
0|id|INTEGER|0||1  
1|name|TEXT(1024)|1||0  
2|file|INTEGER|0||0  
3|parent|INTEGER|0||0  

sqlite> PRAGMA table_info(as_node_closure);  
0|id|INTEGER|0||1  
1|ancestor|INTEGER|0||0  
2|descendant|INTEGER|0||0  
3|depth|INTEGER|0||0  

如何查询数据库以选择祖先节点在给定嵌套级别上匹配给定字符串的所有叶节点？

例如，在简单的英语："give me all leaf nodes where ancestor node is 'trumpet' at a nesting level of 2"

对于上述数据，应该返回muted_trumpet1.wav和legato_trumpet1.wav。

更新1

一些示例数据：

sqlite> select * from as_node
1|BrassP||
2|4Horns||1
3|FT||2
4|DYN_02||3
5|RR_01||4
6|Mic_CL||5
7|4H_CL_DYN2_FT_01.wav|1|6
8|Mic_FM||5
9|4H_FM_DYN2_FT_01.wav|2|8
10|Mic_RM||5
11|4H_RM_DYN2_FT_01.wav|3|10
12|Mic_SUR||5
13|4H_SURR_DYN2_FT_01.wav|4|12
14|DYN_03||3
15|RR_01||14
16|Mic_CL||15
17|4H_CL_DYN3_FT_01.wav|5|16
...

sqlite> select * from as_node_closure limit 10;
1|1|1|0
2|1|2|1
3|2|2|0
4|1|3|2
5|2|3|1
6|3|3|0
7|1|4|3
8|2|4|2
9|3|4|1
10|4|4|0

更新2:

这给了我很多我想要的东西，但我相信这不是正确的方法：

SELECT name from as_node WHERE id IN (SELECT descendant FROM as_node_closure WHERE ancestor IN (SELECT id FROM as_node WHERE name = "trumpet") AND descendant IN (SELECT id FROM as_node WHERE name LIKE "%.wav"));

这使用内部SELECT获取所有叶节点，并仅返回该列表中的后代。

Answer 1

我想这就是你想要的(编辑)：

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me 
    leaf.*                                 -- all
FROM 
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            -- 
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            -- 
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

在上进行测试(在这里使用'FT'而不是'trumpet'来匹配数据)。