英文:
How to flatten parent-child hierarchy from bottom up using CTE
问题
在Stack Overflow上，我有以下问题，我有一个层次结构表格，如下所示：
表格 TEST
- [wbe_node_type] [varchar](50) NOT NULL, /* 叶子节点或展开节点 */
- [wbe_node] [varchar](50) NOT NULL,
- [wbe_node_mask] [varchar](50) NOT NULL,
- [wbe_description] [varchar](50) NOT NULL,
- [wbe_parent] [varchar](50) NOT NULL,
- [wbe_node_niveau] [int] NOT NULL, /* 记录在层次结构中的级别 */
数据如下：
wbe_node_type, wbe_node, wbe_node_mask, wbe_omschrijving, wbe_parent, wbe_node_niveau
expanded, AFCARN0, A-FCARN0, Project 1, [NULL], 0
leaf, AFCARN04010, A-FCARN0.40.10, Description 2, AFCARN040, 2
expanded, AFCARN040, A-FCARN0.40, Realisatie, AFCARN0, 1
具有wbe_node_type为'leaf'的行是层次结构中的最底层，最大深度为8级。我想要将层次结构展平为从底层（'leaf'节点）到顶层的一行数据。
我的想法是从wbe_node_type = 'leaf'的记录开始，然后找到其上面的所有级别。这可以使用临时表轻松完成。然而，我用于查询此数据的工具不支持临时表。因此，我想尝试使用CTE。
当我在线查找示例时，这些示例总是从顶部开始，然后向下工作。是否可能从底部开始（从最低级别开始）填充CTE？
英文:
for my first question on stack overflow I have the following problem, I've got an hierarchy table that looks as follows:
Table TEST
- [wbe_node_type] [varchar](50) NOT NULL, /* leaf or expanded */
- [wbe_node] [varchar](50) NOT NULL,
- [wbe_node_mask] [varchar](50) NOT NULL,
- [wbe_description] [varchar](50) NOT NULL,
- [wbe_parent] [varchar](50) NOT NULL,
- [wbe_node_niveau] [int] NOT NULL, /* level of the record in the hierarchy */
The data looks as follows:
wbe_node_type, wbe_node, wbe_node_mask, wbe_omschrijving, wbe_parent, wbe_node_niveau
expanded, AFCARN0, A-FCARN0, Project 1, [NULL]. 0
leaf, AFCARN04010, A-FCARN0.40.10, Description 2, AFCARN040, 2
expanded, AFCARN040, A-FCARN0.40, Realisatie, AFCARN0, 1
the row with the wbe_node_type 'leaf' is the lowest level in the hierarchy, maximum depth is 8 levels deep. What I want is to flatten the hierarchy in one row from the bottom level (the 'leaf' node) to the top level.
My thought was to start to find al records with wbe_node_type = 'leaf' and find all the levels above it. This is easy to do with a temp-table. However, the tool I'm using to query this data doesn't work with temp-tables. So I wanted to try it using a CTE.
When I'm looking for examples online, these always start at the top and work their way down. Is it possible to populate a CTE from the bottom starting with the lowest levels?
答案1
得分: 0
不确定百分之百，但可能是这样的。
SELECT
*
FROM TEST l3
Inner JOIN TEST l2 ON l3.wbe_parent = l2.wbe_node
Inner JOIN TEST l1 ON l2.wbe_parent = l1.wbe_node
WHERE
l3.wbe_node = 'AFCARN04010';
似乎有效。
英文:
Not 100% sure on this but perhaps something like this.
SELECT
*
FROM TEST l3
Inner JOIN TEST l2 ON l3.wbe_parent = l2.wbe_node
Inner JOIN TEST l1 ON l2.wbe_parent = l1.wbe_node
WHERE
l3.wbe_node = 'AFCARN04010'
Seems to work
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论