这篇文章主要介绍了postgresql 实现字符串分割字段转列表查询，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。

 

在数据查询中，有一张a表存有另一张b表的id并以‘，'隔开

 

如：

 

 

假设现在要关联查询关于 b表的一些信息，怎么办。

 

分割查询：字符串转列表函数 ：regexp_split_to_table()

 

1select * from regexp_split_to_table ((select product_ids from fee_project_meal where id = 116199376233182210 ), ',')

 

 

查询后，字符串就变成了列表，然后你就可以根据这个列表去找b表的相关信息了。

 

select *

from pm.product

where id::text in

(select * from regexp_split_to_table ((select product_ids from bp.fee_project_meal where id = 116199376233182210 ), ','))

 

 

 

 

首先数据验证是正确的，说明sql没有问题，接下来就是一起关联查询了

 

1.因为这个a表与b表是一对多的关系，所以我们先关联出多条。

 

select a.id as "a表_id",

a.name as "a表_name",

p.name as "b表_name"

from bp.fee_project_meal a

LEFT JOIN pm.product p on p.id::text

in (select * from regexp_split_to_table ((select product_ids from bp.fee_project_meal where id = a.id ), ','))

where a.id = 116199376233182210

 

 

 

 

2.还有一种就是 我只要查出a表的数据，b表的数据中某些字段做未拼接的形式存在，也就是说 现在要查出a表的数据

 

 SELECT

 a.id as "a表_id",

  a.name as "a表_name",

  bb.p_id as "b表_拼接id",

  bb.p_name as "b表_拼接name"

 from bp.fee_project_meal a

  left join (

select a.id as "bb_id",String_agg(p.id::text,',') as "p_id",String_agg(p.name::text,',') as "p_name"

from bp.fee_project_meal a

LEFT JOIN pm.product p on

p.id::text in (select * from regexp_split_to_table ((select product_ids from bp.fee_project_meal where id = a.id ), ','))

GROUP BY 1

) bb on bb."bb_id" = a.id

 

 

以上就是，字符串字段的拆解查询。

 

补充：pgsql 查询字段中根据逗号分隔的字符串的的 个数

 

1select length(translate(column,','||column,','))+1 from table