PostgreSQL 结果集用逗号分割和以逗号拆分显示

1.行数据使用于逗号分隔显示

select string_agg(v,',') v
from(
  select '0.0' v union all
  select '10.0' v union all
  select '20.0' v union all
  select '30.0' v union all
  select '40.0' v 
  )s;

结果:

1.以逗号分隔的数据转为行显示

select regexp_split_to_table(v, ',') v
from(select '0.0,10.0,20.0,30.0,40.0'::text v)s;

结果:

3.1.以逗号分隔的数据转为列显示

select s[1] v1,s[2] v2,s[3] v3,s[4] v4,s[5] v5,
       split_part(s1,',', 1) s1,
       split_part(s1,',', 2) s2,
       split_part(s1,',', 3) s3,
       split_part(s1,',', 4) s4,
       split_part(s1,',', 5) s5
from(
  select regexp_split_to_array('0.0,10.0,20.0,30.0,40.0', ',') s,'0.0,10.0,20.0,30.0,40.0'::varchar s1
)tt;

结果:

 

posted @ 2021-12-16 15:46  VicLW  阅读(2645)  评论(0编辑  收藏  举报