hive执行两表join时，on非相等条件，报错：Both left and right aliases encountered in JOIN ‘1‘_both left and right aliases encountered in join '1-优快云博客

文章讲述了在使用Hive进行SQL查询时，遇到JOIN操作中不支持非相等条件（如DATEDIFF不等于1）的问题。给出了从原始HQL到改进后的HQL的解决方案，即改变JOIN条件为等值连接并调整WHERE子句的逻辑。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 原始报错HQL

1.1 报错HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip tb2
on t1.vin=tb2.vin and DATEDIFF(t1.dt,tb2.dt)=1

1.2 报错原因

hive执行JOIN时，不支持两个表的字段的非相等操作；
报错地方：

DATEDIFF(t1.dt,tb2.dt)=1

2 解决方法

原始HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip tb2
on t1.vin=tb2.vin and DATEDIFF(t1.dt,tb2.dt)=1

改进后的HQL

with single_trip as(
    select
        vin,
        start_soc,
        end_soc,
        start_mileage,
        end_mileage,
        start_timestamp,
        end_timestamp,
        dt
    from dws_electricity_single_trip_detail
    where substr(dt,1,7)='2023-09'
)
select
  *
from single_trip t1
left join single_trip t2
on t1.vin=t2.vin
where DATEDIFF(t2.dt,t1.dt)=1