sql - 在 postgres Sql 中按级别和 regexp_substr 连接

Question

我正在将查询从 oracle 迁移到 postgres sql。我面临函数 regexp_substr 和 regexp_substr 双重连接的问题。这是查询，

SELECT 
ID,
DATE,
ADDRESS,
NAME,
LASTNAME,
DATEOFBIRTH,
AGID
FROM (
SELECT DISTINCT 
App.AggId AS AggId, 
App.Id AS Id,  
App.LastName AS LastName, 
App.Name AS Name,
App.Date AS Date, 
App.DateOfBirth AS DateOfBirth, 
App.Address AS Address, 
FROM 
App App
WHERE    
App.DATE between {?DateCreatedFrom} and ({?DateCreatedTo}) and 
({?dFrom}=-999 or App.ID>={?IdFrom}) and
({IdTo} = -999 or App.ID <= {?IdTo}) and
({?AgId} = 'ALL' or App.AGID in (  select regexp_substr( {?AgId},'[^,]+', 1, level) from dual
connect by regexp_substr( {?AgId}, '[^,]+', 1, level) is not null       ) ) and
({?LastName} = 'null' or App.LASTNAME = {?LastName}) and
({?Name} = 'null' or App.NAME = {?Name}) 
ORDER BY ID DESC 

请帮助我在 postgres 中运行此查询

标签： sqlpostgresqlsplit

Answer 1

我相信

select regexp_substr( {?AgId},'[^,]+', 1, level) from dual
connect by regexp_substr( {?AgId}, '[^,]+', 1, level) is not null;

是相同的：

SELECT unnest(string_to_array({?AgId}, ','));

我不是 Oracle 语法专家，但我相信它只是将 ',' 上的字符串拆分为多行。

您也可以使用 regexp_split_to_table，但它往往比 unnest(string_to_array...