我有一个表使用Adjacency List模型存储分层信息. (使用自引用键 – 下面的示例.此表可能看起来像
familiar)：
category_id name parent ----------- -------------------- ----------- 1 ELECTRONICS NULL 2 TELEVISIONS 1 3 TUBE 2 4 LCD 2 5 PLASMA 2 6 PORTABLE ELECTRONICS 1 7 MP3 PLAYERS 6 8 FLASH 7 9 CD PLAYERS 6 10 2 WAY RAdioS 6
将上述数据“压扁”成这样的东西的最佳方法是什么？
category_id lvl1 lvl2 lvl3 lvl4 ----------- ----------- ----------- ----------- ----------- 1 1 NULL NULL NULL 2 1 2 NULL NULL 6 1 6 NULL NULL 3 1 2 3 NULL 4 1 2 4 NULL 5 1 2 5 NULL 7 1 6 7 NULL 9 1 6 9 NULL 10 1 6 10 NULL 8 1 6 7 8
每行是通过层次结构的一个“路径”,除了每个节点(不仅是每个叶节点)都有一行. category_id列表示当前节点,“lvl”列表示其祖先.当前节点的值也必须位于最右边的lvl列中. lvl1列中的值将始终表示根节点,lvl2中的值将始终表示lvl1的直接后代,依此类推.
解决方法
在一个简单的邻接列表中进行多级查询总是涉及自左连接.制作一个右对齐表格很容易：
SELECT category.category_id,ancestor4.category_id AS lvl4,ancestor3.category_id AS lvl3,ancestor2.category_id AS lvl2,ancestor1.category_id AS lvl1
FROM categories AS category
LEFT JOIN categories AS ancestor1 ON ancestor1.category_id=category.category_id
LEFT JOIN categories AS ancestor2 ON ancestor2.category_id=ancestor1.parent
LEFT JOIN categories AS ancestor3 ON ancestor3.category_id=ancestor2.parent
LEFT JOIN categories AS ancestor4 ON ancestor4.category_id=ancestor3.parent;
像你的例子那样左对齐它有点棘手.想到这一点：
SELECT category.category_id,ancestor1.category_id AS lvl1,ancestor4.category_id AS lvl4
FROM categories AS category
LEFT JOIN categories AS ancestor1 ON ancestor1.parent IS NULL
LEFT JOIN categories AS ancestor2 ON ancestor1.category_id<>category.category_id AND ancestor2.parent=ancestor1.category_id
LEFT JOIN categories AS ancestor3 ON ancestor2.category_id<>category.category_id AND ancestor3.parent=ancestor2.category_id
LEFT JOIN categories AS ancestor4 ON ancestor3.category_id<>category.category_id AND ancestor4.parent=ancestor3.category_id
WHERE
ancestor1.category_id=category.category_id OR
ancestor2.category_id=category.category_id OR
ancestor3.category_id=category.category_id OR
ancestor4.category_id=category.category_id;
would work for n-tier hierarchies.
抱歉,在邻接列表模型中无法进行任意深度查询.如果您经常进行此类查询,则应将模式更改为other models of storing hierarchical information之一：完全邻接关系(存储所有祖先 – 后代关系),物化路径或嵌套集.
如果类别不会移动很多(这通常是像你的例子一样的商店),我会倾向于嵌套集.
