Oracle含CONNECT BY的查询迁移至PostgreSQL求助

以下是你提供的Oracle查询对应的PostgreSQL版本，重点解决了CONNECT BY的替代问题：

WITH readyForHouseKeeper AS (
  SELECT regexp_substr(system_file_path, '^.*/') AS path
  FROM cognito.patent p
  JOIN cognito.pat_tracking pt
    ON p.ref_id = pt.pat_ref_id
   AND p.language = 'es'
   AND p.stage_id = 80
   AND pt.stage_id = 80
   AND pt.status_date BETWEEN (localtimestamp - INTERVAL '2 months') AND (localtimestamp - INTERVAL '1 month') -- 筛选60天到30天前的数据
  ORDER BY pt.status_date
  LIMIT 50000 -- 替代Oracle的rownum < 50001
)
SELECT h.path || ' ' || file_type || ' 30 * f rm'
FROM readyForHouseKeeper h
CROSS JOIN (
  SELECT trim(regexp_split_to_table('stripped_cxml_*;MachineOutput_NameFile_cxml_*;Low_Confidence_*;*ContentFile*', ';')) AS file_type
) ft;

关键改动说明 #

1. 替换rownum为LIMIT #

Oracle的rownum < 50001用于限制返回前50000条数据，PostgreSQL中用LIMIT 50000实现相同效果。注意PostgreSQL需要将ORDER BY和LIMIT放在同一条SELECT语句中，才能保证先排序再取数（原Oracle中ORDER BY搭配rownum无法保证排序生效，因为Oracle会先生成rownum再执行排序）。

2. 替换CONNECT BY字符串拆分逻辑 #

原Oracle通过层级查询CONNECT BY拆分分号分隔的字符串，PostgreSQL提供了更简洁的regexp_split_to_table函数，直接将字符串按指定分隔符拆分成多行。配合trim()去除拆分后元素的多余空格，和原逻辑完全一致。

3. 语法细节调整 #

• PostgreSQL中INTERVAL支持'2 months'写法（和Oracle的'2' MONTH等效，这里采用更符合PostgreSQL习惯的写法）
• 显式使用CROSS JOIN替代原查询的隐式笛卡尔积写法，让SQL逻辑更清晰（原查询的逗号连接表等价于CROSS JOIN）

内容的提问来源于stack exchange，提问作者Asad Rao