postgresql 相当于postgres中的SQL Server choose和string_split

46qrfjad 于 2023-05-28 发布在 PostgreSQL

关注(0)|答案(2)|浏览(158)

我最近优化了一个SQL Server过程。从这个

select id, 'col1' as valtype, col1 as val from table1 where col1 > 0
union all
select id, 'col2', col2 from table1 where col2 > 0
union all
select id, 'col3', col3 from table1 where col3 > 0

对此：

select * 
from 
    (select 
         id, valtype,
         case valtype
             when 'col1' then col1
             when 'col2' then col2
             when 'col3' then col3
         end as val
     from 
         table1 
     cross join 
         (VALUES ('col1'), ('col2'), ('col3')) t(valtype)
    ) a 
where 
    val > 0

对此：

select * 
from 
    (select 
         id, value as valtype,
         choose(ordinal, col1, col2, col3) as val
     from 
         table1
     cross join 
         string_split('col1,col2,col3', ',', 1) t
    ) a 
where val > 0

那么，我该如何在Postgres中实现这个过程呢？
除了CASE之外，是否有与CHOOSE等效的函数？
是否有一个string_to_array也返回index？

postgresql

来源：https://stackoverflow.com/questions/76344597/equivalent-of-sql-server-choose-and-string-split-in-postgres

2条答案

按热度按时间

ffdz8vbo1#

如果你觉得choose()函数方便又有用，你可以在Postgres中轻松创建它：

create or replace function choose(bigint, variadic anyarray)
returns anyelement language sql immutable as $$
    select $2[$1]
$$;

使用string_to_table() with ordinality代替string_split()：

select * 
from (
    select 
        id, value as valtype,
        choose(ordinal, col1, col2, col3) as val
    from table1
    cross join string_to_table('col1,col2,col3', ',') with ordinality as t(value, ordinal)
    ) a 
where val > 0
order by 1, 2

在db<>fiddle.中测试

赞(0）回复(0）举报 2023-05-28

oxf4rvwz2#

我不认为有CHOOSE()的postgresql等价物，但是像string_split这样的东西可以用unnest with ordinality和string_to_array来完成，如下所示：

select * from (
  select id, valtype,
    case valtype
      when 'col1' then col1
      when 'col2' then col2
      when 'col3' then col3
    end as val
  from table1
  cross join unnest(string_to_array('col1,col2,col3', ',') ) WITH ORDINALITY a(valtype, nr)
) a where val > 0

赞(0）回复(0）举报 2023-05-28

我来回答

postgresql 相当于postgres中的SQL Server choose和string_split

2条答案

相关问题

热门标签

最新问答