SQL数据查询以及使用left join出现重复情况以及解决方法

原因：表之间关联的关系不是一对一的，可能是一对多的所以会把那张多的表数据也就查询出来，导致数据重复。
例子：
insert into t_teacher values(1,‘小马哥’);
insert into t_teacher values(2,‘大盛哥’);
select * from t_teacher

insert into t_place values(1,‘办公室’);
insert into t_place values(2,‘厕所’);
insert into t_place values(3,‘教室’);
select * from t_place

•  		查询出所有的结果
  

select * from t_teacher,t_place,t_behavior
where
(t_teacher.id = 1 or t_teacher.id = 2)and
(t_place.id = 2 or t_place.id = 1)and
(t_behavior.id = 3 or t_behavior.id = 2)

•  			两行数据合并成一行
  

  select * from t_teacher,t_place,t_behavior
  where t_behavior.id = 2 and t_place.id = 2 and t_teacher.id=2
  

•  			把三张表数据合并在一行
   	 	select * from t_teacher,t_place,t_behavior
   	where 
   	(t_teacher.id = 2)and
   	(t_place.id = 1)and 
   	(t_behavior.id = 2)
   	union
   	select* from t_teacher,t_place,t_behavior
   	where
   	(t_teacher.id = 1)and
   	(t_place.id = 1)and
   	(t_behavior.id = 2)
   	union
   	select* from t_teacher,t_place,t_behavior
   	where
   	(t_teacher.id = 1)and
   	(t_place.id = 2 )and
   	(t_behavior.id = 3)
  

•  		把三张表数据合并在一起
  

select
t_behavior.id,
t_behavior.behavior_action,
t_teacher.name,
t_place.place_name
from t_behavior,t_teacher,t_place
where
t_behavior.behavior_user = t_teacher.id
and
t_behavior.behavior_place = t_place.id

2.select
c.id,c.behavior_action,t.name,p.place_name
from t_behavior c
left join t_teacher t on c.behavior_user = t.id
left join t_place p on c.behavior_place = p.id

• 使用left join查询重复数据

  1.创表
  create table t_behavior(
  id int primary key ,
  behavior_action varchar(20),
  behavior_user int,
  behavior_place int
  );
  insert into t_behavior values(1,‘看书’,2,2);
  insert into t_behavior values(2,‘抽烟’,1,1);
  insert into t_behavior values(3,‘喝奶’,1,2);
  insert into t_behavior values(4,‘抽烟’,null,2);
  2.查表

3.删表 drop table t_behavior; 4.再创表 create table t_behavior( id int , behavior_action varchar(20), behavior_user int, behavior_place int ); insert into t_behavior values(1,‘看书’,2,2); insert into t_behavior values(2,‘抽烟’,1,1); insert into t_behavior values(3,‘喝奶’,1,2); insert into t_behavior values(4,‘抽烟’,null,2); 执行后的结果再执行以下语句 insert into t_behavior values(2,‘抽烟’,1,1); 5.数据则会重复

• 使用left join查询重复数据2
  create table t_teacher
  ( id int primary key,
  name varchar(20) );
  insert into t_teacher values(1,‘小马哥’);
  insert into t_teacher values(2,‘小马哥’);
  insert into t_teacher values(3,‘大盛哥’);
  select * from t_teacher

删表： drop table t_teacher;
再创表 create table t_teacher
( id int ,
name varchar(20) );
insert into t_teacher values(1,‘小马哥’);
insert into t_teacher values(2,‘小马哥’);
insert into t_teacher values(3,‘大盛哥’);
select * from t_teacher
再插一遍
insert into t_teacher values(2,‘小马哥’);

•  		解决重复的方案 
  

解决方案：
治标：distinct
治本：检查重复记录，然后删除
怎么检查：select id,name,COUNT(1)from t_teacher
group by id,name
Having COUNT(id)>1 怎么删除：方案很多的
a.插入一个子查询 insert into t_teacher(id,name)
( select id+10000 id,name
from t_teacher
group by id,name )
b.删除旧的重复数据 delete from t_teacher where id<10000
c.修改记录 update t_teacher set id = id-10000
2.存在关联不上的数据（维表的数据缺失）
------补数据：（手动定期更新）
select id,name from t_teacherselect id,MAX(name)
from t_teacher group by id select id,name,COUNT(1)
from t_teacher
group by id,name
Having COUNT(id)>1

a.插入一个子查询
insert into t_teacher(id,name)
(
select id+10000 id,name
from t_teacher
group by id,name
)

2.删除旧的重复数据
delete from t_teacher where id<10000

update t_teacher set id = id-10000select distinct * from t_teacher
select * from t_behavior
update t_behavior set behavior_user=3 where id=4