列在SQL连接后在derived_table1中不存在

Question

我写了这个sql代码来查询红移数据

Select
tenant_name, date, fk_organization_unit, active_member_per_studio, cn
From
(
SELECT
tenant_name, date, fk_organization_unit, active_member_per_studio
from
fct.st_member_development as st_member_development
inner join
(
SELECT
tenant_name as tn, fk_organization_unit as fk, type, date as dt, contract_count as cn
from
fct.st_contract_development
)
u
on  st_member_development.tenant_name =u.tn
and st_member_development.fk_organization_unit =u.fk
and st_member_development.date =u.dt
)
x
limit 5

我得到了以下错误错误:列"不存在

我该如何解决这个问题

Answer 1

您得到此错误，因为列cn从第二个SELECT中缺失。试试这个:

Select tenant_name, date, fk_organization_unit, active_member_per_studio, cn
From (
SELECT tenant_name, date, fk_organization_unit, active_member_per_studio, cn   
from fct.st_member_development as st_member_development
inner join
(SELECT tenant_name as tn, fk_organization_unit as fk, type, date as dt, contract_count as cn from fct.st_contract_development) u
on  st_member_development.tenant_name =u.tn and st_member_development.fk_organization_unit =u.fk and st_member_development.date =u.dt
)
limit 5

Answer 2

内部查询中缺少cn列

Select
tenant_name, date, fk_organization_unit, active_member_per_studio, cn
From
(
SELECT
tenant_name, date, fk_organization_unit, active_member_per_studio, cn
from
fct.st_member_development as st_member_development
inner join
(
SELECT
tenant_name as tn, fk_organization_unit as fk, type, date as dt, contract_count as cn
from
fct.st_contract_development
)
u
on  st_member_development.tenant_name =u.tn
and st_member_development.fk_organization_unit =u.fk
and st_member_development.date =u.dt
)
x
limit 5