如何通过SQL找出2个表里值不同的列的方法

以下有两个表,他们的结构完全相同,请通过SQL找出值不同的列。

Student_1

NAMEAGESCOREpeter26100jack2596daniel2648bark2169

Student_2

NAMEAGESCOREpeter2689jack2596daniel2648bark2169

方法一 — NOT EXISTS:复制代码
代码如下:SELECT *FROM Student_1 S1WHERE NOT EXISTS (SELECT * FROM
Student_2 S2 WHERE S1.name = S2.name AND S1.age = S2.age AND S1.score =
S2.score )UNION ALLSELECT *FROM STUDENT_2 S2WHERE 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_1MINUSSELECT * FROM Student_2)UNION
ALL(SELECT * FROM Student_2MINUSSELECT * FROM Student_1)

方法三 — HAVING GROUP BY复制代码
代码如下:SELECT DISTINCT name, age, score FROM (SELECT * FROM
Student_1UNION ALLSELECT * FROM Student_2)GROUP BY name, age, score
HAVING COUNT(*)=1 ;

发表评论

电子邮件地址不会被公开。 必填项已用*标注