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)；
