SQL查询连接后的歧义列

Question

我使用红移来查询数据我使用了以下查询:

Select tenant_name
From (
SELECT *  from fct.st_member_development as st_member_development
inner join
(SELECT *  from fct.st_contract_development) u
on  st_member_development.tenant_name =u.tenant_name and st_member_development.fk_organization_unit =u.fk_organization_unit and st_member_development.date=u.date
)
limit 5

对于我要连接的表它们都有tenant_name列

和我的查询失败，出现以下错误:模棱两可的

你知道怎么解决这个问题吗?

Answer 1

Select u.tenant_name
From fct.st_member_development as st_member_development
inner join fct.st_contract_development u
on  st_member_development.tenant_name = u.tenant_name and                         
st_member_development.fk_organization_unit =u.fk_organization_unit and 
st_member_development.date=u.date
limit 5

Answer 2

显然，最简单的解决方案是不使用子查询。

如果您正在使用子查询，那么您可以列出所需的列:

Select mc.tenant_name
from (select md.tenant_name 
from fct.st_member_development md join
fct.st_contract_development cd
on md.tenant_name = cd.tenant_name and  
md.fk_organization_unit = cd.fk_organization_unit and
md.date = cd.date
) mc
limit 5;

(注意使用表别名来简化编写查询)

如果唯一重复的列名是JOIN中使用的列，那么您也可以使用USING:

select mc.tenant_name
from (select *
from fct.st_member_development md join
fct.st_contract_development cd
using (tenant_name, fk_organization_unit, md.date)
) mc
limit 5;

USING子句防止重复被包含在SELECT *中。