SYS_CONNECT_BY_PATH和START WITH / CONNECT BY PostgreSQL等效 [英] SYS_CONNECT_BY_PATH and START WITH/CONNECT BY PostgreSQL Equivalent

问题描述

我有一个查询，它是我需要转换为PostgreSQL的，用Oracle写的更大查询的一部分。

I have a query that is part of a much larger query written in Oracle that I need to convert to PostgreSQL.

/*rn and cnt are defined earlier*/
SELECT wtn, LTRIM(SYS_CONNECT_BY_PATH(RESP_TCSI, ','),',') TCSI_CODES
FROM DATA
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1
AND PRIOR WTN = WTN

据我所知，Postgres中不存在与 SYS_CONNECT_BY_PATH（）等效的功能。我知道Postgres在tablefunc中有一个 CONNECTBY（）函数，但是我不认为它可以做开头和按位连接。我也知道与 LTRIM（）等效的Postgres是什么，但是如果我必须使用 CONNECTBY（）或类似的东西，我不确定修剪字符串是否重要。

From what I'm able to tell, there's not an equivalent to SYS_CONNECT_BY_PATH() in Postgres. I know that Postgres has a CONNECTBY() function in tablefunc, but I don't think it does what either the start with and connect by bits do. I'm also aware of what the Postgres equivalent to LTRIM() is, but if I have to use CONNECTBY() or something similar, I'm not sure if trimming the string is important.

阅读和搜索时，我发现可能有一种方法可以通过递归进行选择，但是我不确定该怎么做，除此之外，我不太了解代码在做什么。我的假设是，它与基于Oracle等效项的分层树有关，但是即使那样我也不确定。

Reading and searching around I noticed that there is probably a way to do this with some recursive select, but I'm unsure how I would do that, and beyond that, I don't really understand what the code is doing. My assumption would be that it has something to do with a hierarchical tree, based on the Oracle equivalents, but even then I'm not sure. How would I do something equivalent or similar to this in Postgres?

谢谢。

推荐答案

使用递归公用表表达式：

with recursive tree as (
  select wtn, 
         resp_tcsi as tcsi_codes
   from data
   where rn = 1 -- this is the "start with" part

   union all

   select ch.wtn, 
          p.tcsi_codes||','||ch.resp_tcsi 
   from data as ch
    join tree p 
      on ch.rn -1 = p.rn -- this is the "connect by" part
     and ch.wtn = p.wtn
)
select *
from tree;

这篇关于SYS_CONNECT_BY_PATH和START WITH / CONNECT BY PostgreSQL等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！