以下有两个表,他们的结构完全相同,请通过sql找出值不同的列。
student_1
name |
age |
score |
peter |
26 |
100 |
jack |
25 |
96 |
daniel |
26 |
48 |
bark |
21 |
69 |
student_2
name |
age |
score |
peter |
26 |
89 |
jack |
25 |
96 |
daniel |
26 |
48 |
bark |
21 |
69 |
方法一 -- not exists:
select *
from student_1 s1
where not exists
(select *
from student_2 s2
where s1.name = s2.name
and s1.age = s2.age
and s1.score = s2.score
)
union all
select *
from student_2 s2
where not exists
(select *
from student_1 s1
where s1.name = s2.name
and s1.age = s2.age
and s1.score = s2.score
);
方法二 -- minus
(select * from student_1
minus
select * from student_2)
union all
(select * from student_2
minus
select * from student_1)
方法三 -- having group by
select distinct name, age, score from (
select * from student_1
union all
select * from student_2
)group by name, age, score having count(*)=1 ;
如对本文有疑问,
点击进行留言回复!!
网友评论