根据机构名称和出版物计数合并作者别名

问：

以下作者发表了来自不同机构的许多论文。 我可以将一些别名与笛卡尔连接起来。是否可以将所有别名映射到一个主要作者？

with t1 as 
(

select 'jones-william-j' as alias,'jones-w' as last_name_init,'The Institute Of Cancer Research, London'as institute_name,1 as pub_count
union all select 'jones-w-j','jones-w','University Of California',42
union all select 'jones-w-j','jones-w','Western University',2
union all select 'jones-willam-j','jones-w','University Of California',2
union all select 'jones-william','jones-w','University Of California',12
union all select 'jones-william-j','jones-w','University Of California',429
union all select 'jones-william-j','jones-w','University Of San Diego',1
union all select 'jones-william-j','jones-w','Va San Diego Healthcare System',2
union all select 'jones-william-j','jones-w','Western University',7

)
select * from t1;

笛卡尔连接给出了以下 2 位作者 jones-w-1 和 jones-w-2，是否可以将它们合并为一个author_id jones-w-1？

with t1 as 
(

select 'jones-william-j' as alias,'jones-w' as last_name_init,'The Institute Of Cancer Research, London'as institute_name,1 as pub_count
union all select 'jones-w-j','jones-w','University Of California',42
union all select 'jones-w-j','jones-w','Western University',2
union all select 'jones-willam-j','jones-w','University Of California',2
union all select 'jones-william','jones-w','University Of California',12
union all select 'jones-william-j','jones-w','University Of California',429
union all select 'jones-william-j','jones-w','University Of San Diego',1
union all select 'jones-william-j','jones-w','Va San Diego Healthcare System',2
union all select 'jones-william-j','jones-w','Western University',7

)
select distinct
     a.last_name_init||'-'||DENSE_RANK() OVER (ORDER BY a.last_name_init,a.institute_name ASC)  author_id,
a.alias,
a.last_name_init,
a.institute_name,
a.pub_count
 from t1 a,
t1 b
  where 1=1
  and a.institute_name = b.institute_name
  and a.last_name_init = b.last_name_init
  and a.alias <> b.alias;

结果如下。

期望的结果是。