postgresql 實現字符串分割字段轉列表查詢

Posted on by

在數據查詢中，有一張a表存有另一張b表的id並以‘，’隔開

如：

假設現在要關聯查詢關於 b表的一些信息，怎麼辦。

分割查詢：字符串轉列表函數 ：regexp_split_to_table()

select * 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 查詢字段中根據逗號分隔的字符串的的 個數

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

參見：

1.translate 與replace類似是替換函數，但translate是一次替換多個單個的字符。

2.基本用法，字符對應替換。

例子：

select translate('1234567','123' ,'abc') from dual ;--1替換為a,2替換為b,3替換為c

結果：abc4567 。

3.如果 沒有對應字符則替換為null;

select translate('1234567','123' ,'ab') from dual；--3替換為null;

結果：ab4567.

4.如果對應字符過多，不影響

select translate('1234567','123' ,'abccd') from dual；

結果：abc4567

5.如果替換字符整個為空字符 ，則直接返回null

select translate('1234567','123' ,'') from dual；

結果：null;

6.如果想篩掉對應字符，應傳入一個不相關字符，同時替換字符也加一個相同字符；

select translate('1234567','&123' ,'&') from dual;

結果：4567；

7.如果相同字符對應多個字符，按第一個；

select translate('12334567','1233' ,‘abcd') from dual;

結果：abcc4567;

8.如果想保留某些特定字符篩選掉其他的，比如篩掉漢字保留數字

先把數字篩選掉，

select translate('你師看瞭3三樓2的6開8發','#0123456789' ,'#') from dual

再用篩選出的漢字去篩選原來的語句留下數字，

select translate('你師看瞭3三樓2的6開8發','#'||translate('你師看瞭3三樓2的6開8發','#0123456789' ,'#'),'#') from dual；

結果：3268；

9.還有其他靈活用法，比如我可以判斷兩個字符串如果：字符串都是數字字符，然後數字字符的順序不同，且每個字符隻出現一次，

我可以判斷他們包含的數字是不是完全一致；

比如比較123 和132；

select 1 from dual where
translate('0123456789','123' ,'aaaaaaaaaa') =translate('0123456789','132' ,'aaaaaaaaaa')

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：