目录
1.replace into 重复记录保留规则
(1)情景1: union all 中重复
结论:保留最后union all 中的记录
replace into test.temp_test
select 'jim' , 1 union all
select 'jim' , 2 union all
select 'jim' , 3;
(2)order by 中重复
结论:保留order by 排序中最后一条
create table test.temp_test_01 as
select 'jim' name , 1 id union all
select 'jim' , 2 union all
select 'jim' , 3 union all
select 'jack' , 1 union all
select 'jack' , 2 union all
select 'jack' , 3 union all
select 'tom' , 1 union all
select 'tom' , 2 union all
select 'tom' , 3
;
replace into test.temp_test select * from test.temp_test_01 order by name , id desc;
replace into test.temp_test select * from test.temp_test_01 order by name , id asc;
2.group by 重复记录保留规则
源数据:
(1)默认情况
结果:默认保留select排序中第一条
select * from test.temp_test_01 group by name ;
(2)order by 排序情况
结果: 不管嵌套中正或反排序,还是和默认情况一样
(3)嵌套加上limit 999999999
结果: 嵌套倒排序后 加上 limit 99999999 后 ,取最后一条
注意:limit 99999999 必须加,否则不会排序
结果: 嵌套正排序后 加上 limit 99999999 后 ,取第一条