PostgreSQL：获取具有相似列值的记录 [英] Postgresql: Get records having similar column values

问题描述

Table A

id   name   keywords
1    Obj1   a,b,c,austin black
2    Obj2   e,f,austin black,h
3    Obj3   k,l,m,n
4    Obj4   austin black,t,u,s
5    Obj5   z,r,q,w

我需要获取包含类似关键字类型的记录。因此，该表的结果需要为：

I need to get those records which contains similar type of keywords. Hence the result for the table needs to be:

Records:
1,2,4

由于记录 1,2,4 是其中某些或另一关键字与at匹配的记录

Since records 1,2,4 are the one whose some or the other keyword match with at least any other keyword.

推荐答案

您可以将 csv转换为数组，然后使用Postgres的数组函数：

You can convert the "csv" to an array and then use Postgres' array functions:

select *
from the_table t1
where exists (select *
              from the_table t2
              where string_to_array(t1.keywords, ',') && string_to_array(t2.keywords, ',')
              and t1.id <> t2.id);

这篇关于PostgreSQL：获取具有相似列值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！