sql – 将邻接列表层次结构展平为所有路径的列表

我有一个表使用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的直接后代,依此类推.

如果可能,生成此输出的方法将在SQL中,并且适用于n层层次结构.

解决方法

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之一：完全邻接关系(存储所有祖先 – 后代关系),物化路径或嵌套集.

如果类别不会移动很多(这通常是像你的例子一样的商店),我会倾向于嵌套集.

（编辑：天瑞地安资讯网_黄海网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!