如何获取层次结构表的路径 [英] How to get the path of an hierarchy table
问题描述
我一直在为如何处理这种情况而苦苦挣扎:
I've been struggling a bit about how to deal with this situation:
我有一个表，其结构如下:
I have a table structured as follow:
Family_code | Parent_Family_Code | ....
1 2
2 4
3 6
4 3
......................
当用户搜索特定的家庭代码时，我需要返回整个路径(最多10个级别)，例如，对于family_code = 1，我需要:
When a user is searching for a specific family code, I need to return the entire path (up to 10 levels max) , so for example for family_code = 1 I'll need:
Family_code | parent_1 | p_2 | p_3 | p_4 | p_5 | .....
1 2 4 3 6 null null.....
我知道我可以使用sys_connect_by_path()它将为我带来预期的结果，但可以是字符串，而不是单独的列，这是我希望避免的事情.
I know I can use sys_connect_by_path() which will bring me the expected result but as a string, and not as separate columns which is something I'll prefer to avoid.
这也可以通过向同一个表进行10次左联接来完成，或者使用LEAD()/LAG()函数来完成，该函数将包括很多子查询，并且将使查询变得凌乱且难以理解，但是话又说回来，这会更多那么应该是沉重的，我需要尽可能地简化它.
This can also be done with 10 left joins to the same table, or the use of LEAD()/LAG() functions which will include a lot of sub queries and will make a messy and unreadable query, but then again, this will be more heavy then it should be and I need to simplify it as I can.
我已经提出了使用substr()函数的解决方案(代码的长度始终为varchar2(3)):
I've come up with a solution using substr() function(the length of the codes will always be varchar2(3)):
SELECT s.family_code,
s.parent_family_code_1,
s.parent_family_code_2,
CASE WHEN length(s.family_path) - (4 * 3 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 3 + 2), 3) ELSE NULL END as parent_family_code_3,
CASE WHEN length(s.family_path) - (4 * 4 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 4 + 2), 3) ELSE NULL END as parent_family_code_4,
CASE WHEN length(s.family_path) - (4 * 5 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 5 + 2), 3) ELSE NULL END as parent_family_code_5,
CASE WHEN length(s.family_path) - (4 * 6 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 6 + 2), 3) ELSE NULL END as parent_family_code_6,
CASE WHEN length(s.family_path) - (4 * 7 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 7 + 2), 3) ELSE NULL END as parent_family_code_7,
CASE WHEN length(s.family_path) - (4 * 8 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 8 + 2), 3) ELSE NULL END as parent_family_code_8,
CASE WHEN length(s.family_path) - (4 * 9 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 9 + 2), 3) ELSE NULL END as parent_family_code_9,
CASE WHEN length(s.family_path) - (4 * 10 + 2) > 0 THEN substr(s.family_path, length(s.family_path) - (4 * 10 + 2), 3) ELSE NULL END as parent_family_code_10
FROM (SELECT t.family_code,
t.parent_family_code as parent_family_code_1,
prior t.parent_family_code as parent_family_code_2,
sys_connect_by_path(t.family_code, ',') as family_path
FROM table t
connect by prior t.family_code = t.parent_family_code) s
但是我想要一个不使用子字符串的解决方案，因为当其他开发人员接触它时，对其进行任何维护都将更加困难 . 所以基本上我的问题是-如何在不使用子字符串的情况下将整个路径选择为不同的列?
But I would like a solution without the use of substrings since it will be harder to do any maintaince on it when other developers will touch it . So basically my question is - how do I select the entire path as different columns without the use of substrings?
推荐答案
使用PIVOT子句对@ MT0答案进行了稍微修改的查询.
A slightly modified query from @MT0 answer, using PIVOT clause.
SELECT *
FROM (
select connect_by_root( family_code ) as Family_code,
'P_' || level lev_el,
parent_family_code
from table_name t
start with not exists(
select 1 from table_name t1
where t.family_code = t1.parent_family_code )
connect by prior parent_family_code = family_code
)
PIVOT (
max( parent_family_code )
FOR (lev_el) IN (
'P_1', 'P_2', 'P_3', 'P_4', 'P_5', 'P_6','P_7', 'P_8','P_9','P_10' ,
'P_11', 'P_12', 'P_13', 'P_14', 'P_15', 'P_16','P_17', 'P_18','P_19','P_20',
'P_21', 'P_22', 'P_23', 'P_24', 'P_25', 'P_26','P_27', 'P_28','P_29','P_30'
/* add more "levels" here if required */
)
);
从@ MT0答案中查询示例数据的结果(@ MT0，感谢您提供示例数据):
A result of the query for sample data from @MT0 answer (@MT0, thank you for providing sample data):
FAMILY_CODE 'P_1' 'P_2' 'P_3' 'P_4' 'P_5' 'P_6' 'P_7' 'P_8' 'P_9' 'P_10' 'P_11' 'P_12' 'P_13' 'P_14' 'P_15' 'P_16' 'P_17' 'P_18' 'P_19' 'P_20' 'P_21' 'P_22' 'P_23' 'P_24' 'P_25' 'P_26' 'P_27' 'P_28' 'P_29' 'P_30'
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 4 5 6
8 7 9 10 11
这篇关于如何获取层次结构表的路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
