sql - 匹配值列表中的字符串并创建新行

Question

这是为这里的所有 SQL 开发人员准备的，这很容易，但我想发布。我有一张表，其中包含世界上所有国家/地区的名称。我有另一张 table ，上面有一个句子，里面有多个国家的名字。如何提取国家名称并将它们放在单独的行中，如下所示

Country_Universe_Table
美国
印度
不丹
尼泊尔
伊朗

dataid 评论
1 美国和印度签署了一项协议(protocol)
2 英国、尼泊尔和不丹出现寒冷天气

输出应为
dataid 国家
1 美国
1 印度
2 英国
2 尼泊尔
2 不丹

Answer 1

以下是做你想做的不完美的方法:

select c.dataid, cut.country
from Country_Universe_Table cut join
     comments c
     on c.comment like '%' || cut.country || '%'

这只是在评论表中查找国家/地区字符串。但是，它将“American”匹配到“America”，将“UKELELE”匹配到“UK”。这可能足以满足您的需求。

编辑:

您可以通过将评论拆分为单词然后使用横向连接进行比较来增强这一点(在较新版本的 Postgres 中):

select c.dataid, cut.country
from comments c cross join lateral
     regexp_split_to_table(c.comment, E'[.,-() ]+') as w(word) join
     Country_Universe_Table cut
     on w.word = cut.country;