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之一：完全连接相关(存储全部祖先 – 儿女相关),物化路径或嵌套集.

假如种别不会移动许多(这凡是是像你的例子一样的市肆),我会倾向于嵌套集.

（编辑：湖南网）

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