如何使用閉包表查詢層次結構中的葉節點？

我有一些分層數據（代表樂器和演奏技巧），其中層次結構中的每個葉子都是一個音頻文件。

葉節點的嵌套深度會有所不同，因為一些演奏技巧會有子發音。

葉節點的範例路徑可能是

/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.wavand 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來獲取所有葉節點並僅返回該列表中的後代。

我認為這就是您想要的（已編輯）：

-- 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
     ) ;

在**SQL-Fiddle**進行測試（使用 where'FT'代替'trumpet', 來匹配您的數據。）

引用自：https://dba.stackexchange.com/questions/57009