hive中间接实现不等值连接

由于hive中不支持不等值连接，给应用带来不便。

create tablelpx_test_a as

select id,class

 from (

select 1 as id, 2 asclass from dual

union all

select 2 as id, 3 asclass from dual

union all

select 3 as id, 4 asclass from dual

union all

select 5 as id, 6 asclass from dual

) a;

 

create tablelpx_test_b as

select id,class

 from (

select 1 as id, 3 asclass from dual

union all

select 2 as id, 3 asclass from dual

union all

select 3 as id, 3 asclass from dual

union all

select 5 as id, 6 asclass from dual

) a;

 

 

--oracle

SELECT a.*, b.id ASb_id, b.class AS b_class

  FROM lpx_test_a a

  LEFT OUTER JOIN lpx_test_b b

    ON (a.id = b.id AND a.class < b.class);

 

--hive

SELECT a.*,

       CASE WHEN a.class < b.class THEN b.idEND AS b_id,

       CASE WHEN a.class < b.class THEN   b.class END AS b_class

  FROM lpx_test_a a

  LEFT OUTER JOIN lpx_test_b b

    ON (a.id = b.id)；