要做您想做的事情，您可能需要cte（公共表表达式），以及 LATERAL 查询。不幸的是，MySQL5.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，但仍然不会得到横向查询。
编辑：我更进一步，以确定重复的名字。如果没有ctes，这个问题看起来越来越难看：

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;

最后我综合使用了以上的答案。首先，我创建了一个临时表，用空字符串修剪并替换“dr.”字符串。

create temporary table if not exists temp_names AS (
select *, 
    case when name like lower('dr. %') then trim(replace(lower(name), lower('dr. %'), ''))
    else name end as plain_name from t);

然后我使用select和groupby将该表中的值与相同的纯名称值连接起来。

select max(name) as name, group_concat(distinct phone_number) as phone_number, address from temp_names 
    group by plain_name, address having count(*) >=1;

这将给出一个包含所需结果的表格：

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

假设这些是完全嵌套的，您可以通过执行以下操作获得“long form”：

select name,
       (select t2.name
        from t t2
        where t2.name like concat('%', t.name, '%')
        order by length(t2.name) desc
        limit 1
       ) as long_form
from t;

然后可以在聚合中使用它。我将使用子查询：

select long_form, group_concat(distinct phone) as phones,
       group_concat(distinct address) as addresses
from (select t.*,
             (select t2.name
              from t t2
              where t2.name like concat('%', t.name, '%')
              order by length(t2.name) desc
              limit 1
             ) as long_form
      from t
     ) tt
group by long_from;