我最近优化了一个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?
2条答案
按热度按时间ffdz8vbo1#
如果你觉得
choose()
函数方便又有用,你可以在Postgres中轻松创建它:使用
string_to_table() with ordinality
代替string_split()
:在db<>fiddle.中测试
oxf4rvwz2#
我不认为有
CHOOSE()
的postgresql等价物,但是像string_split
这样的东西可以用unnest with ordinality
和string_to_array
来完成,如下所示: