Hive 语句错误解决：ODPS-0130071: Semantic analysis exception - column b.contractContractCode cannot be resolved

在Hive环境下执行以下语句创建表时遇到错误：

create table DM_M_YX_HTGL_li as 
select 
    contract_no,
    contract_name,
    bus_number,
    contractBatchCode,
    orderId,
    date_format(start_date, 'yyyy-MM-dd') as start_date,
    date_format(end_date, 'yyyy-MM-dd') as end_date
from(
    select a.* from DM_M_YX_HTGL_YXD_1 a
    left join(
        select contractBatchCode, orderId, serialNumber from ZQ_DWA.DWA_M_EVT_DD_ORDER_INFO
        where month_id = '202307'
    ) b
    on a.contract_no = b.contractContractCode and a.bus_number = b.serialNumber
    left join (
        SELECT arrair_no, start_date, end_date from ZQ_DWD.DWD_D_ACC_ICT_CONTRACT_INFO
        where month_id = '202307'
    ) c
    on a.contract_no = c.arrair_no
)t
group by contract_no, contract_name, bus_number;

错误信息如下：

FAILED: ODPS-0130071:[16,26] Semantic analysis exception - column b.contractContractCode cannot be resolved
ODPS-0130071:[6,5] Semantic analysis exception - column contractBatchCode cannot be resolved
ODPS-0130071:[7,5] Semantic analysis exception - column orderId cannot be resolved
ODPS-0130071:[8,17] Semantic analysis exception - column start_date cannot be resolved
ODPS-0130071:[9,17] Semantic analysis exception - column end_date cannot be resolved

这个错误是因为在子查询的select子句中，使用了表别名b，但是没有在子查询的from子句中定义这个别名。在子查询的from子句中应该添加别名b，并且将b表的字段包括contractBatchCode、orderId、serialNumber也在select子句中进行选择。

修改后的语句如下：

create table DM_M_YX_HTGL_li as 
select 
    contract_no,
    contract_name,
    bus_number,
    contractBatchCode,
    orderId,
    date_format(start_date, 'yyyy-MM-dd') as start_date,
    date_format(end_date, 'yyyy-MM-dd') as end_date
from(
    select a.*, b.contractBatchCode, b.orderId from DM_M_YX_HTGL_YXD_1 a
    left join(
        select contractBatchCode, orderId, serialNumber from ZQ_DWA.DWA_M_EVT_DD_ORDER_INFO
        where month_id = '202307'
    ) b
    on a.contract_no = b.contractContractCode and a.bus_number = b.serialNumber
    left join (
        SELECT arrair_no, start_date, end_date from ZQ_DWD.DWD_D_ACC_ICT_CONTRACT_INFO
        where month_id = '202307'
    ) c
    on a.contract_no = c.arrair_no
)t
group by contract_no, contract_name, bus_number;

修改后的语句在子查询的from子句中添加了别名b，并在select子句中选择了b表的字段，解决了错误。