----------------------------------或者下面這種寫法create database cc_test; use cc_test; table1 可以了解為記錄學生最好成績的表。 table2可以了解為每次學生的考試成績。 我們要始終更新table1的資料 create table table1 ( id string , maxScore string ); create table table2 ( id string , score string ); insert into table1 values (1,100), (2,100), (3,100), (4,100); insert into table2 values (2,100), (3,90), (4,120), (5,100); -----注意這裡2重複 3score減少 4score增加 . 5屬于新增資料 insert overwrite table1 select t1.id , greatest(t1.maxScore,nvl(t2.score,0)) from table1 t1 left join table2 t2 on t1.id =t2.id union all select t2.id , t2.score from table2 t2 where not exists ( select 1 from table1 t1 where t1.id = t2.id )
select t2.id , greatest(nvl(t1.maxScore,0),t2.score) from table2 t2 left join table1 t1 on t1.id =t2.id union all select t1.id , t1.maxScore from table1 t1 where not exists ( select 1 from table2 t2 where t1.id = t2.id )
兩個的最後查詢結果是ok的。
-------------------------------------------------------
最後說下思路。 table1 和table2 兩個表
t2 和t3 相當于id重疊的部分。
因為hive沒有update ,是以一般update = delete+insert 。但是hive也沒有delete。。。
是以oracle的matched not match 的删掉t2 插入t3 然後插入t4。
我們可以看做 插入t1 和插入 t3+t4
也可以看做 插入 t4 和插入 t1+t2
這兩種就對應我們上面的兩種sql
你以為這就完了嗎?怎麼可能 就這麼lowb的結束了。 我們要追尋更深層次的知識海洋。
兩個有什麼差別? 我們該選用那種好呢?
一般來說 table1 是遠大于table2的。 例如學校每年的學生數量都差不多=table2.但是學校曆史學生資料量是很大的=table1.
也不排除 該學校剛剛創立 第一年學生100 人 第二年學生1000人。。
但是一般來說傾向于 table1>>>>table2. 那麼那種效率更高呢?
一般來說 外表大 内表小用in 。 外表小内表大用exists。
exists
insert overwrite table1 select t1.id , greatest(t1.maxScore,nvl(t2.score,0)) from table1 t1 left join table2 t2 on t1.id =t2.id union all select t2.id , t2.score from table2 t2 where not exists ( select 1 from table1 t1 where t1.id = t2.id )
in
insert overwrite table1 select t1.id , greatest(t1.maxScore,nvl(t2.score,0)) from table1 t1 left join table2 t2 on t1.id =t2.id union all select t2.id , t2.score from table2 t2 where t2.id not in ( select id from table1 )
join
insert overwrite table1 select t1.id , greatest(t1.maxScore,nvl(t2.score,0)) from table1 t1 left join table2 t2 on t1.id =t2.id union all select t2.id , t2.score from table2 t2 left join table1 t1 on t1.id =t2.id where t1.maxScore is null
個人來說是推薦用exists 和join這兩種的