SQL报错AnalysisException：内联视图q2存在重复列别名account_id

执行的SQL语句 #

select *

from (

     select * 
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id)  
as q2

inner join

prod_ard119_master.lgd_basel_exp_id_mdp 

on prod_ard119_master.lgd_basel_exp_id_mdp.customer_id = q2.customer_id

触发的错误 #

AnalysisException: duplicated inline view column alias: 'account_id'
in inline view 'q2'

错误原因 #

子查询q2中使用select *关联了crmd_ead和crmd_acc两个表，这两个表都包含account_id列，导致q2的结果集中出现重复的account_id列，SQL引擎无法识别唯一列名，因此抛出该错误。

解决方案 #

方案1：明确指定所需列（推荐）

避免使用select *，只列出实际需要的列，重复的account_id只需保留一个：

select *
from (
     -- 按需指定需要的列，避免重复列
     select crmd_ead.*, crmd_acc.customer_id
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id
) as q2
inner join prod_ard119_master.lgd_basel_exp_id_mdp 
on prod_ard119_master.lgd_basel_exp_id_mdp.customer_id = q2.customer_id

方案2：给重复列添加别名

如果需要保留两个表的所有列，给重复的account_id列添加别名区分：

select *
from (
     select crmd_ead.*, crmd_acc.account_id as acc_account_id, crmd_acc.customer_id
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id
) as q2
inner join prod_ard119_master.lgd_basel_exp_id_mdp 
on prod_ard119_master.lgd_basel_exp_id_mdp.customer_id = q2.customer_id

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