PostgreSQL中基于模式匹配的两表关联实现问询

需求说明 #

• 表O_REMEDY_PEOPLEGROUP（简称op）：包含manager_name（管理者名称）、manager_hierarchy（空格分隔的上级管理者名单）
• 表Org_details（简称od）：包含org_name（组织名称）、vp_details（VP名称）
• 目标：为O_REMEDY_PEOPLEGROUP的每条记录匹配对应的org_name——当vp_details中的VP完整出现在manager_hierarchy的空格分隔名单中时返回对应org_name，否则返回null

解决方案SQL #

SELECT 
    op.manager_name,
    op.manager_hierarchy,
    od.org_name
FROM 
    O_REMEDY_PEOPLEGROUP op
LEFT JOIN 
    Org_details od 
    ON (' ' || op.manager_hierarchy || ' ') ~ (' ' || od.vp_details || ' ');

代码说明 #

1. LEFT JOIN：确保O_REMEDY_PEOPLEGROUP的所有记录都被保留，未匹配到VP的记录org_name自动为null
2. 完整单词匹配：在字段前后拼接空格，用正则运算符~匹配完整的VP名称，避免部分匹配（例如防止把jacob和jacobson误判为匹配）
3. 替代方案：也可以用strpos函数实现相同逻辑，替换ON子句为：

   ON strpos(' ' || op.manager_hierarchy || ' ', ' ' || od.vp_details || ' ') > 0
   

预期输出 #

内容的提问来源于stack exchange，提问作者frp farhan