检索表中的层次结构 [英] Retrieving hierarchy within table
问题描述
我一直在使用查询来检索数据库中文件的层次结构.这是表格的示例:
I've been working with a query to retrieve hierarchy of a file in a database. Here is an example of what the table looks like:
_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.我需要的是一种检索对象的完整路径的方法(意味着/之间的每个数字都等于一个对象).我已经成功地找到了一条东西的整个路径，但是它是多行的.如果可能的话，我希望将其排成一排.
So basically, I figured the last number in HierarchyPath is the HierarchyID of the object in the DB. What I need is a way to retrieve the full path of the object (meaning each number between / is equal to an object). I have succeeded in finding the whole path of something, but in multiple rows. I would like to have it in one row, if possible.
这是我当前的查询和结果(第一个表中的第二个Parallel I-F):
Here is my current query and result (for the second Parallel I-F in the first table):
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之类的东西?
How can I get something like Part/Electronic/Integrated Circuits/Data Acquisition/ADC/Parallel-I-F ?
我目前正在尝试在同一张桌子上使用左联接，但没有成功.我还阅读了有关使用CTE的信息，但是我从未成功使用过CTE.我正在使用SQL开发人员，但不介意其他环境的答案！
I'm currently trying to use a left join on the same table but no success. I also read about using CTE, but I've never been able to successfully use one. I'm on SQL developer, but don't mind other environment answers!
推荐答案
您想要的东西似乎可以实现.但是我不能从您的描述(在这方面很差)中完全弄清您表的结构.因此，我将组成自己的团队来演示如何做到这一点.您必须自己在架构中进行翻译.
What you want seems possible. But I cannot completely figure out your table's structure from your (in this regard poor) description. So I will make up my own to demonstrate how this can be done. You'll have to translate that in your schema yourself.
由于您提到了SQL Developer，所以我猜您正在使用Oracle.您还错过了标记正在使用的DBMS的功能.
And since you mentioned SQL Developer, I guess you're using Oracle. You also missed to tag the DBMS you're using.
表OBJECT存储具有名称和ID的对象.
The table OBJECT stores the objects with name and ID.
CREATE TABLE OBJECT
(ID NUMBER(38),
NAME VARCHAR2(8));
表HIERARCHY将对象的路径存储为对象的ID字符串，并用'/'和ID分隔.
The table HIERARCHY stores paths of objects as a string of their IDs, separated by '/' and an ID.
CREATE TABLE HIERARCHY
(ID NUMBER(38),
PATH VARCHAR2(8));
现在，我们首先需要一个数字表，该表的整数从1到HIERARCHY中路径中对象的最大值.我们可以为此使用递归CTE.
Now first thing we need is a number table having integers from 1 to the maximum of objects in a path in HIERARCHY. We can use a recursive CTE for that.
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()中.
REGEXP_COUNT(HIERARCHY.PATH, '/') will count the occurrences of '/'in the path. By adding 1 to it, we get the number of objects in the path. And we want the maximum, so we wrap it in MAX().
现在，我们可以将CTE联接到HIERARCHY的所有行，以便HIERARCHY的每一行的出现频率与行路径中对象的数量相同(如果路径为空，则为一次)字符串，等效于Oracle中的NULL.
Now we can left join that CTE to all rows of HIERARCHY, so that each row of HIERARCHY occurs as often as the number of objects in the path of the row (or one time if the path is the empty string, which is equivalent to NULL in Oracle).
FROM HIERARCHY
LEFT JOIN CTE
ON CTE.I <= REGEXP_COUNT(HIERARCHY.PATH, '/') + 1
我们可以再次使用REGEXP_COUNT(HIERARCHY.PATH, '/') + 1合并所有小于或等于路径中对象数量的数字.
We once again can use REGEXP_COUNT(HIERARCHY.PATH, '/') + 1 to join all numbers, that are less or equal the number of objects in the path.
CTE.I现在将对HIERARCHY中的行进行编号，对于路径中的每个对象，从1到路径中的整数总数(或者，如果路径为NULL，则为NULL)有一个整数.
CTE.I will now number the rows from HIERARCHY, for each object in the path there's one integer from 1 to the total number of integers in the path (or NULL, if the path is NULL).
我们可以在联接结果中使用CTE.I来从路径中提取对象ID，该对象ID在路径中的第CTE.I位置.为此，我们可以使用REGEXP_SUBSTR():
We can use CTE.I in the result of the join to extract the object ID from the path, that is at the CTE.I-th position in the path. For this, we can use REGEXP_SUBSTR():
REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I)
模式匹配字符串开头或'/'之后不是'/'的所有字符.第四个参数CTE.I告诉函数返回第CTE.I个匹配项.这就是我们获取路径中各个位置的相关对象ID的方式.不幸的是，在返回的匹配项开始处可能不需要'/'，因此我们将其包装在REGEXP_REPLACE()中以将其删除.
The pattern matches all characters, that are not a '/' after the beginning of the string or after a '/'. The fourth parameter, CTE.I, tells the function to return the CTE.I-th match. That's how we get the relevant object ID for the respective position in the path. Unfortunately there might be unneeded '/' at the beginning of the returned matches, so we wrap it in a REGEXP_REPLACE() to remove them.
REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/')
这样，我们现在可以离开联接OBJECT.我们只添加了一个TO_NUMBER().
With that, we can now left join OBJECT. We just throw in an additional TO_NUMBER().
LEFT JOIN OBJECT
ON OBJECT.ID = TO_NUMBER(REGEXP_REPLACE(REGEXP_SUBSTR(HIERARCHY.PATH, '(^|/)[^/]+', 1, CTE.I), '^/'))
加入OBJECT即将完成.现在我们在输出中想要的GROUP BY列HIERARCHY，例如HIERARCHY.ID和HIERARCHY.PATH，然后使用LISTAGG()将对象名称重新连接到由'/'分隔的路径.
Having OBJECT joined we're almost done. We now GROUP BY the columns of HIERARCHY we want in the output, e.g. HIERARCHY.ID and HIERARCHY.PATH, and use LISTAGG() to concatenate the object names to a path separated by '/' again.
LISTAGG(OBJECT.NAME, '/') WITHIN GROUP (ORDER BY CTE.I) OBJECT_PATH
ORDER BY CTE.I确保每个对象名称在路径中的正确位置.
The ORDER BY CTE.I makes sure every object name is in the right place in the path.
我们在一起得到:
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;
这篇关于检索表中的层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
