Power BI执行SQL Server查询时出现‘column specified multiple times’错误

问题根源在于你的SQL使用了select *，两次left join操作后，DF1表和关联的DF_H/DF_D表都包含ID列，导致最终结果集出现重复的ID列名。SQL Server运行时会自动给重复列名添加后缀（如ID1），但Power BI对结果集的列名唯一性要求更严格，因此抛出错误。

解决方案1：明确指定查询列（推荐） #

放弃select *，只选择需要的列，对重复的ID仅保留其中一个（因关联条件为a.ID = b.ID，二者值完全一致），同时保证Union前后两个结果集的列名、数量、类型一致：

select 
    a.ID, 
    a.Category, 
    b.Status, 
    b.Status_Date
from [DF1] a 
left join [DF_H] b on a.ID = b.ID 
where b.Status is not null 
  and year(b.Status_Date) != '1900' 
  and a.Category = 2
Union 
select 
    a.ID, 
    a.Category, 
    b.Claim_Status as Status, 
    b.Status_Date
from [DF1] a 
left join [DF_D] b on a.ID = b.ID
where b.Claim_Status is not null 
  and year(b.Status_Date) != '1900' 
  and a.Category = 2

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

若确实需要保留两个表的ID列（实际无必要，因关联后值相同），可给列加别名区分：

select 
    a.ID as DF1_ID, 
    b.ID as DF_H_ID, 
    a.Category, 
    b.Status, 
    b.Status_Date
from [DF1] a 
left join [DF_H] b on a.ID = b.ID 
where b.Status is not null 
  and year(b.Status_Date) != '1900' 
  and a.Category = 2
Union 
select 
    a.ID as DF1_ID, 
    b.ID as DF_D_ID, 
    a.Category, 
    b.Claim_Status as Status, 
    b.Status_Date
from [DF1] a 
left join [DF_D] b on a.ID = b.ID
where b.Claim_Status is not null 
  and year(b.Status_Date) != '1900' 
  and a.Category = 2

额外优化 #

你的查询中使用left join但又通过where过滤关联表的非空字段，这等价于inner join，修改后可提升查询效率：

select 
    a.ID, 
    a.Category, 
    b.Status, 
    b.Status_Date
from [DF1] a 
inner join [DF_H] b on a.ID = b.ID 
where year(b.Status_Date) != '1900' 
  and a.Category = 2
Union 
select 
    a.ID, 
    a.Category, 
    b.Claim_Status as Status, 
    b.Status_Date
from [DF1] a 
inner join [DF_D] b on a.ID = b.ID
where year(b.Status_Date) != '1900' 
  and a.Category = 2

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