mysql比较两张表中两个字段值

两张表中两个字段值"/>

mysql比较两张表中两个字段值

以下有两个表，他们的结构完全相同，请通过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 ;