postgresql 字符串分割字段转列表查询

悠悠 2023-05-31 12:28 22阅读 0赞

用法1：

如图：想要将parent\_nums 中的字符串拆分成列表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70][]

select  
   t.id,  
    t.tab\_interest  
from (  
    select id, regexp\_split\_to\_table(parent\_nums, ',') as tab\_interest from  pm.temp\_cate  
) t  where  t.tab\_interest != ''；

结果：

![2021052810164941.png][]

用法二：

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

如：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 1][]

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

分割查询:字符串转列表函数 ：regexp\_split\_to\_table()

select \* from regexp\_split\_to\_table ((select product\_ids from fee\_project\_meal where id = 116199376233182210 ), ',')

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 2][]

查询后，字符串就变成了列表，然后你就可以根据这个列表去找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 ), ','))  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 3][]

首先数据验证是正确的，说明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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 4][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 5][]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70]: /images/20230531/733bba7746dc41c6beb3f4a93f8683b0.png
[2021052810164941.png]: /images/20230531/900044ee06bd40afa47dff37e43bfd28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 1]: /images/20230531/d35a449e12d54e34bb8f3db575576cd9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 2]: /images/20230531/fe03c93a997d485c9aa9f5e4a294d938.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 3]: /images/20230531/9fed9428afe24f83ba337aa9bb043496.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 4]: /images/20230531/883e0d0657e644f58628fae97083db35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Ric2phY2s_size_16_color_FFFFFF_t_70 5]: /images/20230531/1e8b19438dea42f9bd1133f6b695f892.png