PostgreSQL两表数据比对并填充目标表的SQL实现求助

核心思路 #

要完成需求，分两步执行即可：

• 从table1.field2的SQL语句字符串里，提取出[schema].[函数名]格式的函数标识（比如svc_lz.fn_abc）
• 将提取到的函数名与table2.field3关联，把匹配到的table2.field4值填充到输出字段中，无匹配则留空（NULL）

前提假设 #

假设函数名格式为字母、下划线、点号组成的结构化名称，且每个field2里最多有一个需要匹配的函数名（多函数场景后面单独说明）。

以SQL Server为例的实现代码 #

WITH ExtractedFunctions AS (
    SELECT
        field1,
        field2,
        -- 提取符合格式的函数名：定位到第一个函数名起始位置，截取到左括号前结束
        SUBSTRING(
            field2,
            PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', field2 COLLATE SQL_Latin1_General_CP1_CS_AS),
            CHARINDEX('(', SUBSTRING(field2, PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', field2 COLLATE SQL_Latin1_General_CP1_CS_AS), LEN(field2))) - 1
        ) AS extracted_function
    FROM table1
)
SELECT
    ef.field1,
    ef.field2,
    t2.field4 AS field3  -- 输出表的field3对应table2的field4
FROM ExtractedFunctions ef
LEFT JOIN table2 t2
    ON ef.extracted_function = t2.field3;

代码解释 #

1. CTE部分（ExtractedFunctions）：
   • PATINDEX定位字符串中第一个符合函数名格式的起始位置，COLLATE用于区分大小写（不需要可去掉）
   • SUBSTRING配合CHARINDEX('(')截取到函数名结束位置（SQL函数后通常跟左括号(）
2. 关联查询部分：
   • LEFT JOIN确保table1的所有行都被保留，无匹配时输出的field3为NULL
   • 直接将匹配到的table2.field4赋值给输出表的field3

处理多个函数名的情况 #

如果table1.field2里包含多个函数名（比如SELECT * FROM svc_lz.fn_abc() JOIN svc_xy.fn_def()），可以用递归CTE提取所有函数名，再合并匹配的标签值：

WITH RecursiveExtract AS (
    SELECT
        field1,
        field2,
        PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', field2 COLLATE SQL_Latin1_General_CP1_CS_AS) AS start_pos,
        SUBSTRING(field2, PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', field2 COLLATE SQL_Latin1_General_CP1_CS_AS), LEN(field2)) AS remaining_str
    FROM table1
    UNION ALL
    SELECT
        field1,
        field2,
        PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', remaining_str) AS start_pos,
        SUBSTRING(remaining_str, PATINDEX('%[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*%', remaining_str) + CHARINDEX('(', remaining_str), LEN(remaining_str)) AS remaining_str
    FROM RecursiveExtract
    WHERE start_pos > 0
),
ExtractedFunctions AS (
    SELECT
        field1,
        field2,
        SUBSTRING(remaining_str, 1, CHARINDEX('(', remaining_str) - 1) AS extracted_function
    FROM RecursiveExtract
    WHERE start_pos > 0
)
SELECT
    ef.field1,
    ef.field2,
    STRING_AGG(t2.field4, ', ') AS field3  -- 用逗号分隔多个匹配的标签值
FROM ExtractedFunctions ef
LEFT JOIN table2 t2
    ON ef.extracted_function = t2.field3
GROUP BY ef.field1, ef.field2;

其他SQL方言的适配 #

• PostgreSQL：用regexp_matches提取函数名

  WITH ExtractedFunctions AS (
      SELECT
          field1,
          field2,
          (regexp_matches(field2, '[a-z_][a-z0-9_]*\.[a-z_][a-z0-9_]*', 'g'))[1] AS extracted_function
      FROM table1
  )
  SELECT
      ef.field1,
      ef.field2,
      t2.field4 AS field3
  FROM ExtractedFunctions ef
  LEFT JOIN table2 t2 ON ef.extracted_function = t2.field3;
  

• MySQL：用REGEXP_SUBSTR提取函数名

  SELECT
      t1.field1,
      t1.field2,
      t2.field4 AS field3
  FROM table1 t1
  LEFT JOIN table2 t2
      ON REGEXP_SUBSTR(t1.field2, '[a-zA-Z_][a-zA-Z0-9_]*\\.[a-zA-Z_][a-zA-Z0-9_]*') = t2.field3;
  

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