Mysql:连接重复数据但忽略重复项中的字符串

Question

有没有办法在忽略给定字符串的同时查找重复数据？

例如，如果我有一个姓名表，有没有办法连接名称为“Ann Smith”但忽略字符串“Dr.”的行。例如，包含“Ann Smith”和“Dr. Ann Smith”的行应连接成一个名为“Dr. Ann Smith”的行。如果名称匹配(减去“dr.”字符串)并且两行的地址匹配，则连接电话号码。我想采用两个名称中较大的一个，我认为这将涉及使用 MAX 语句。

目前我有一个名为 t: 的表

name          | phone      | address
ann smith     | 1234567899 | 123 home address
dr. ann smith | 1234567890 | 123 home address
brian smith   | 1235551234 | 789 city street

我想要到达:

name          | phone                  | address
dr. ann smith | 1234567890, 1234567899 | 123 home address
brian smith   | 1235551234             | 789 city street

Answer 1

要完成您想要的操作，您可能需要 CTE(通用表表达式)和 LATERAL 查询。不幸的是 MySQL 5.x 没有实现其中任何一个。

以下查询查找重复的名称:

select plain_name, count(*)
  from (
    select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
      from my_table
  ) x
  group by plain_name
  having count(*) > 1

这是朝着正确方向迈出的一步，但您需要进一步处理才能获得您想要的结果。

如果您升级到 MySQL 8，您将获得 CTE，但仍然不会获得 LATERAL 查询。

编辑:我进一步确定了重复的名称。如果没有 CTE，这个查询看起来会越来越难看:

select z.*, y.times
  from (
    select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
      from my_table
  ) z,
  (
    select plain_name, count(*) as times
      from (
        select name, trim(replace(lower(name), lower('Dr.'), '')) as plain_name
          from my_table
      ) x
      group by plain_name
      having count(*) > 1
  ) y
  where z.plain_name = y.plain_name;