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

原创已于 2025-09-27 16:08:40 修改 · 2.8k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#linux #数据库

于 2023-08-18 09:41:45 首次发布

文章介绍了在SQL查询中处理左连接时数据重复的问题，提供了DISTINCT、子查询以及GROUPBY等方法。实例展示了如何使用这些技巧避免左表1条数据与右表多条数据导致的重复结果。

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" ;

5、连表查询–where

select * from t_system_version_range where system_version_range_id in (
select system_version_range_id from  t_release_policy trp where id in (
select release_policy_id from t_configuration_items_v2 tciv where general_configuration_id in (
select id from t_general_configuration_v2 tgcv where modifier !='zhouxi1001'
)
)
)

6、连表查询后删除 --JOIN

DELETE tar
FROM t_system_version_rang tar
JOIN t_release_policy trp ON tar.system_version_range_id = trp.system_version_range_id
JOIN t_configuration_items_v2 tciv ON trp.id = tciv.release_policy_id
JOIN t_general_configuration_v2 tgcv ON tciv.general_configuration_id = tgcv.id
WHERE tgcv.modifier = '${userID}'

7、连表查询后删除查询以为的数据–where

select * from t_system_version_range where system_version_range_id in (
select system_version_range_id from  t_release_policy trp where id in (
select release_policy_id from t_configuration_items_v2 tciv where general_configuration_id in (
select id from t_general_configuration_v2 tgcv where modifier !='zhouxi1001'
)
)
)