高级大数据测试进阶——left jion 重复数据去除处理

1、左表1条数据，右表2条数据，就会出现重复的数据，终级解决办法DISTINCT

select distinct
	    u.id as id ,
	    u.name as name ,
	    p.ip_addr ip 
	from t_user u 
	left join t_login_log p on p.user_id = u.id

2、左表1条数据，右表2条数据，就会出现重复的数据，DISTINCT无法解决，使用子查询来实现

select distinct
	u.id as id ,
	u.name as name ,
	p.ip_addr ip ,
	t2.age
from t_user u 
left join t_login_log p on p.user_id = u.id
left join (select distinct t.age , t.user_id from t_age t) t2 on u.id = t2.user_id 
-- where IFNULL(t2.age,'0') != "0" and IFNULL(p.ip_addr,'0') != "0" 

3、左表1条数据，右表2条数据，就会出现重复的数据，使用group_by关键字来实现

select
u.name,
u.id , 
p.ip_addr
FROM t_user u
left join t_login_log p on p.user_id = u.id
WHERE u.id= '1'
GROUP BY u.id;

4、俄罗斯套娃

select t1.* , t2.age from (
	select distinct
	    u.id as id ,
	    u.name as name ,
	    p.ip_addr ip 
	from t_user u 
	left join t_login_log p on p.user_id = u.id
	-- where IFNULL(p.ip_addr,'0') != "0" 
) t1
left join t_age t2 on t1.id = t2.user_id 
-- where IFNULL(t2.age,'0') != "0" ;