在 Oracle 中反转这条路径 z/y/x 到 x/y/z
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1381256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reverse in Oracle this path z/y/x to x/y/z
提问by Jean-Philippe Martin
How would I do in a SELECT query to reverse this path :
我将如何在 SELECT 查询中反转此路径：
z/y/x
for
为了
x/y/z
where / is the delimiter and where there can be many delimiters in a single line
其中 / 是分隔符，一行中可以有多个分隔符
ex: select (... z/y/x/w/v/u ...) reversed_path from ...
回答by knittl
You can get your result by connecting the reverted components, then reverting the resulting string again. Just make sure you strip your starting separator and put it on the other side:
您可以通过连接恢复的组件，然后再次恢复结果字符串来获得结果。只要确保去掉起始分隔符并将其放在另一侧：
SELECT '/' || REVERSE(LTRIM(SYS_CONNECT_BY_PATH(REVERSE(x), '/'), '/') AS reversed_path
...
回答by Vincent Malgrat
The simplest way would probably be to write a stored pl/sql function, however it can be done with SQL (Oracle) alone.
最简单的方法可能是编写一个存储的 pl/sql 函数，但是它可以单独使用 SQL (Oracle) 来完成。
This will decompose the path in subpath:
这将分解子路径中的路径：
SQL> variable path varchar2(4000);
SQL> exec :path := 'a/b/c/def';
PL/SQL procedure successfully completed
SQL> SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path, ROWNUM rk
2 FROM dual
3 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1;
SUB_P RK
----- --
a 1
b 2
c 3
def 4
We then recompose the reversed path with the sys_connect_by_path:
然后我们使用以下命令重新组合反向路径sys_connect_by_path：
SQL> SELECT MAX(sys_connect_by_path(sub_path, '/')) reversed_path
2 FROM (SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path,
3 ROWNUM rk
4 FROM dual
5 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1)
6 CONNECT BY PRIOR rk = rk + 1
7 START WITH rk = length(regexp_replace(:path, '[^/]', '')) + 1;
REVERSED_PATH
-------------
/def/c/b/a
回答by shahkalpesh
Are you looking for REVERSE?
i.e
你在找反转吗？
IE
SELECT REVERSE('z/y/x') FROM DUAL;
回答by tobwoerk
Found another solution herethat seems flexible, lean and I find quite easy to understand:
在这里找到了另一个看起来灵活、精益的解决方案，我觉得很容易理解：
SELECT son_id,
dad_id,
son_name,
SYS_CONNECT_BY_PATH (son_name, '/') AS family_path
FROM ( SELECT son_id,
dad_id,
son_name,
CONNECT_BY_ISLEAF AS cbleaf
FROM family
START WITH son_id IN (1, 2, 3, 4, 5)
CONNECT BY PRIOR dad_id = son_id)
WHERE CONNECT_BY_ISLEAF = 1
START WITH cbleaf = 1
CONNECT BY PRIOR son_id = dad_id
回答by Alok P
select
listagg(n.name,'\') within group (order by level desc)
from nodes n
start with n.id = :childid
connect by prior n.parentid = n.id
order by level desc;
order by level desc. will concatenate the ancestors in the lineage first, since you are starting with the childid
按级别 desc 排序。将首先连接谱系中的祖先，因为您从 childid 开始
回答by Lalith
@Jean-Philippe Martin @OMG Ponies
@Jean-Philippe Martin @OMG 小马
Try this query,
试试这个查询，
SELECT REGEXP_SUBSTR(PATH,'[^/]+',1,4) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,3) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,2) || '/' || REGEXP_SUBSTR(PATH,'[^/]+',1,1) "Reverse of Path"
FROM (SELECT 'a/bc/def/ghij' PATH FROM DUAL);
This will do it I guess :-)
我猜这会做到:-)
