欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

如何通过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 ;