主页 / dba / 问题 / 255805
Accepted
dd_a
Asked: 2019-12-18 07:26:00 +0800 CST

如何递归更新相同的字符串？

  • 772

我有一个包含这样名称的列的表：

id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan Bourdillion Traherne

我有第二张表，上面有名字列表，像这样

id, first_name
1, Emily
2, John
3, George
4, Suzan
5, Paul
6, Alan
7, Mary
8, Mick
9, Bourdillion
10, Jim
11, Cole

我想删除第一个表中的名字，以获得：

id, employee
1, Mr. Thornton
2, Mr. Mckenzie
3, Mr. McDoughal
4, Ms. Flemming
5, Mr. Traherne

无论有多少名字，我都想删除它们而不调用多次我的 first_names 表，我想知道如果不使用函数中的循环，这是否可能。

我试过这样的查询：

WITH RECURSIVE name AS (   SELECT REPLACE(t1.employee, t2.first_name, '') sec_name 
                        FROM  t1, t2 
                        WHERE position(t2.first_name in t1.employee) > 0 ) 
SELECT sec_name FROM name ;

但是我得到的输出与输入中的名字一样多，例如：

Id, sec_name
1, John Thornton
1, Cole Thornton
2, Paul Mckenzie
2, George Mckenzie
...

我的 Postgres 版本是 9.6 。

任何帮助都感激不尽 ！

postgresql recursive

2 个回答

  1. Best Answer
    WITH RECURSIVE cte AS (
    SELECT employee, 1 id
    FROM t1
    UNION ALL
    SELECT REPLACE(employee, first_name, ''), id+1
    FROM cte
    JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte 
WHERE id > ( SELECT MAX(id) 
             FROM t2 )

    小提琴

    如果t2.id值不以 1 开头或有遗漏，则必须t2使用 ROW_NUMBER() 重新枚举 CTE 中的记录。

    更新。

    可能的问题 - somefirst_name是 some 的子字符串employee（例如 John 和 Johnson）。如果是这样，请使用空格作为附加包装：

    WITH RECURSIVE cte AS (
SELECT employee || ' ' employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, ' ' || first_name || ' ', ' '), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte 
WHERE id > ( SELECT MAX(id) 
             FROM t2 )

    小提琴

    PS。如果需要，修剪多余的尾随空间。

    • 1

  2. 您可以使用regexp_replace交替出现的名字，以替换为空字符串。在这种情况下不需要递归。

    型号为：

    SELECT regexp_replace(fullname,
  '\m(firstname1|firstname2|firstname3|...)\M ', -- note the ending space!
   '',
   'g')
from...

    \m并\M在单词边界匹配，确保不发生部分名称匹配。最后的空格是为了避免匹配姓氏，如果它恰好与列表中的名字重合。当只有一个名字而不是两个名字时，它也可以工作，即使您的样本数据总是有两个。

    如果名字可能包含正则表达式特有的非字母字符，则需要用反斜杠引用它们，如下所示：

    CREATE FUNCTION quote_meta(text) RETURNS text AS $$
  select regexp_replace($1, '([\[\]\\\^\$\.\|\?\*\+\(\)])', '\\\1', 'g');
$$ language sql strict immutable;

    然后可以通过像这样聚合所有名字来形成交替：

    SELECT string_agg(quote_meta(first_name), '|') FROM table

    最后，通过将上述部分组合成这样的查询，可以在一次通过中完成表中的全局更新：

    WITH replacement AS (
 SELECT id,
     regexp_replace(employee,
        concat (
          '\m(',
          (SELECT string_agg(quote_meta(first_name), '|') FROM table_first_name),
          ')\M ' -- note the ending space!
     ),
     '',
     'g') AS newval
   FROM table_employees
)
UPDATE table_employees
   SET employee = newval
  FROM replacement
 WHERE replacement.id = table_employees.id
   AND employee <> newval;

    警告：这是未经测试的。

    • 1

相关问题