时间戳

首页 > 解决方案 > 检索表中的层次结构

sql - 检索表中的层次结构

问题描述

我一直在使用查询来检索数据库中文件的层次结构。以下是该表的示例：

_Name_               _HierarchyPath_                
Parallel EEPROM     163796003/1761551443/413793741/1362244494/110367462/3988861187/3597067685/4208992221    
Parallel Flash      163796003/1761551443/413793741/1362244494/110367462/3988861187/3597067685/1995340606    
Parallel I-F        163796003/1761551443/413793741/1362244494/110367462/391521622/2389021280/3222611234 
Parallel I-F        163796003/1761551443/413793741/1362244494/110367462/391521622/4124681422/3222611234 
Parallel, In-line   163796003/1761551443/413793741/977119157/977119157/1065183491/4216548299/92850509/1330595286    
Serial\Parallel I-F 163796003/1761551443/413793741/1362244494/110367462/391521622/4124681422/92930422

所以基本上，我认为最后一个数字HierarchyPath是HierarchyID数据库中对象的数字。我需要的是一种检索对象完整路径的方法（意味着之间的每个数字/都等于一个对象）。我已经成功地找到了某物的整个路径，但是在多行中。如果可能的话，我想把它排成一排。

这是我当前的查询和结果（Parallel I-F第一个表中的第二个）：

SELECT *
FROM WC.CLASSIFICATIONNODE
WHERE substr(HIERARCHYID, - instr(reverse(HIERARCHYID), '/') + 1)     IN         ('163796003', '1761551443', '413793741', '1362244494', '110367462', '391521622', '4124681422', '3222611234')
ORDER BY HIERARCHYID;

结果是：

Part                163796003/1761551443/413793741
Electronic          163796003/1761551443/413793741/1362244494
Integrated Circuits 163796003/1761551443/413793741/1362244494/110367462
Data Acquisition    163796003/1761551443/413793741/1362244494/110367462/391521622
Parallel I-F        163796003/1761551443/413793741/1362244494/110367462/391521622/2389021280/3222611234
ADC                 163796003/1761551443/413793741/1362244494/110367462/391521622/4124681422
Parallel I-F        163796003/1761551443/413793741/1362244494/110367462/391521622/4124681422/3222611234
Data Acquisition    163796003/1761551443/413793741/1362244494/40756919/3258224989/2899710639/391521622

我怎样才能得到类似的东西Part/Electronic/Integrated Circuits/Data Acquisition/ADC/Parallel-I-F？

我目前正在尝试在同一张桌子上使用左连接，但没有成功。我还阅读了有关使用 CTE 的信息，但我从来没有成功使用过一个。我是 SQL 开发人员，但不介意其他环境答案！

标签： sqloracle

解决方案

你想要的似乎是可能的。但是我无法从您的（在这方面很差的）描述中完全弄清楚您的表格结构。因此，我将自己制作来演示如何做到这一点。您必须自己在架构中翻译它。

既然你提到了 SQL Developer，我猜你正在使用 Oracle。您还错过了标记您正在使用的 DBMS。

该表OBJECT存储具有名称和 ID 的对象。

CREATE TABLE OBJECT
             (ID NUMBER(38),
              NAME VARCHAR2(8));

该表HIERARCHY将对象的路径存储为其 ID 的字符串，由 ID 分隔'/'。

CREATE TABLE HIERARCHY
             (ID NUMBER(38),
              PATH VARCHAR2(8));

现在我们需要的第一件事是一个数字表，其中包含从 1 到路径中对象最大值的整数HIERARCHY。我们可以为此使用递归 CTE。

WITH CTE(I)
AS
(
SELECT 1 I
       FROM DUAL
UNION ALL
SELECT CTE.I + 1 I
       FROM CTE
       WHERE CTE.I <= (SELECT MAX(REGEXP_COUNT(HIERARCHY.PATH, '/')) + 1
                              FROM HIERARCHY)
)

REGEXP_COUNT(HIERARCHY.PATH, '/')将计算'/'路径中出现的次数。通过给它加 1，我们得到路径中对象的数量。我们想要最大值，所以我们把它包装在MAX().

现在我们可以将该 CTE 左连接到 的所有行HIERARCHY，以便每一行的HIERARCHY出现频率与该行路径中的对象数量一样多（如果路径是空字符串，则为一次，这相当于NULL在 Oracle 中） .

FROM HIERARCHY
     LEFT JOIN CTE
               ON CTE.I <= REGEXP_COUNT(HIERARCHY.PATH, '/') + 1

我们再次可以使用REGEXP_COUNT(HIERARCHY.PATH, '/') + 1连接所有数字，即小于或等于路径中对象的数量。

CTE.I现在将从 开始对行进行编号HIERARCHY，对于路径中的每个对象，都有一个从 1 到路径中整数总数的整数（或者NULL，如果路径是NULL）。

我们可以CTE.I在连接的结果中使用从路径中提取对象 ID，即路径中的CTE.I第 -th 位置。为此，我们可以使用REGEXP_SUBSTR()：

REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I)

该模式匹配所有字符，不是'/'字符串开头之后的 a 或 a 之后的字符'/'。第四个参数CTE.I告诉函数返回第CTE.I-th 匹配项。这就是我们获取路径中相应位置的相关对象 ID 的方式。不幸的是，在返回的匹配项开始时可能不需要'/'，因此我们将其包装在 aREGEXP_REPLACE()中以删除它们。

REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/')

有了这个，我们现在可以离开 join 了OBJECT。我们只是添加了一个额外的TO_NUMBER().

LEFT JOIN OBJECT
          ON OBJECT.ID = TO_NUMBER(REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/'))

OBJECT加入后，我们几乎完成了。我们现在在输出中使用我们想要GROUP BY的列HIERARCHY，例如HIERARCHY.IDand HIERARCHY.PATH，并使用LISTAGG()将对象名称连接到'/'再次分隔的路径。

LISTAGG(OBJECT.NAME, '/') WITHIN GROUP (ORDER BY CTE.I) OBJECT_PATH

确保每个ORDER BY CTE.I对象名称都在路径中的正确位置。

我们一起得到：

WITH CTE(I)
AS
(
SELECT 1 I
       FROM DUAL
UNION ALL
SELECT CTE.I + 1 I
       FROM CTE
       WHERE CTE.I <= (SELECT MAX(REGEXP_COUNT(HIERARCHY.PATH, '/')) + 1
                              FROM HIERARCHY)
)
SELECT HIERARCHY.ID HIERARCHY_ID,
       HIERARCHY.PATH HIERARCHY_PATH,
       LISTAGG(OBJECT.NAME, '/') WITHIN GROUP (ORDER BY CTE.I) OBJECT_PATH
       FROM HIERARCHY
            LEFT JOIN CTE
                      ON CTE.I <= REGEXP_COUNT(HIERARCHY.PATH, '/') + 1
            LEFT JOIN OBJECT
                      ON OBJECT.ID = TO_NUMBER(REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/'))
       GROUP BY HIERARCHY.ID,
                HIERARCHY.PATH
       ORDER BY HIERARCHY.ID;

db<>小提琴

推荐阅读