Postgre(pg)-SQL脚本记录

1、关键词组匹配内容查询脚本

 --根据词组，查询文本中匹配到的关键词
select t1.title,string_agg(t2.keys, ',')
from tb_consulation t1
  left join 
   (select distinct regexp_split_to_table('中国人民共和国|浙汪省|黑龙汪省','\|') keys) t2 
  on position(t2.keys in t1.title) > 0
where t1.title like any (ARRAY['%中国人民共和国%','%浙汪省%','%黑龙汪省%']) 
group by t1.title

--变身使用：查询关键词出现的次数
select t2.keys, count(t2.keys) 
 from 
      ( 
        select title 
		from tb_consulation WHERE title LIKE ANY (ARRAY ['%办里%','%注消%'])
      ) t1 
left join 
      ( 
        select distinct regexp_split_to_table('办里|注消','\|') keys 
      ) t2 
  on position(t2.keys in t1.title) > 0 
	group by t2.keys ;