如何通过SQL找出2个表里值不同的列的方法
程序员文章站
2024-02-22 21:00:40
以下有两个表,他们的结构完全相同,请通过sql找出值不同的列。
student_1
name
age
score
peter
26
100...
以下有两个表,他们的结构完全相同,请通过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 ;