Postgresql子查询中包含字符串文本的语句 - Postgresql subquery wheere in statement with string text

Postgresql subquery wheere in statement with string text

我的子查询没有起到积极的作用。请帮助。列元素是字符串，用逗号分隔;

select  id, title, elements, (
    select string_agg(distinct street, ',') 
    from locations 
    where elementnames in (replace(quote_nullable(elements),',',''','''))
) as buildings
from events ;

    id     | title |      elements       | buildings
-----------+-------+---------------------+----------
 124003    | alpha | NYCID005            |
 000111    | beta  | NYCID222            | 
 200123    | gamma | NYCID201,NYCID193   | 
 102036    | sigma | NYCID202,NYCID191   | 
(4 rows)

我找到了一些解决方案。

    select id,title,elements,
    (select string_agg(distinct street, ',') from locations
    where (string_to_array(elementnames,',') <@ (string_to_array(elements,',')))) as buildings
from events;
    id     | title |      elements       |           buildings       |
-----------+-------+---------------------+---------------------------+
124003 | alpha      | NYCID005           | Ford,Harrison             |
000111 | beta       | NYCID222           | Stallone,Malkovitch       |
200124 | gamma      | NYCID201,NYCID193  | Gates Street,Ostin        |