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

前端之家收集整理的这篇文章主要介绍了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之一:完全邻接关系(存储所有祖先 – 后代关系),物化路径或嵌套集.

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

原文链接:https://www.f2er.com/mssql/83755.html

猜你在找的MsSQL相关文章