我有两个表:

T1
A
B
C
D

T2
A
B
E
F
G


现在我要查询将合并这两个表但排除的查询
相同的记录。输出表应类似于:

T1T2
C
D
E
F
G


怎么做?

最佳答案

看起来您需要FULL OUTER JOIN并排除公共部分。您可以使用以下方法进行模拟:

SELECT T1.col_name
FROM T1
LEFT JOIN T2
  ON T1.col_name = T2.col_name
WHERE T2.col_name IS NULL
UNION
SELECT T2.col_name
FROM T2
LEFT JOIN T1
  ON T1.col_name = T2.col_name
WHERE T1.col_name IS NULL;


SqlFiddleDemo

╔══════════╗
║ col_name ║
╠══════════╣
║ C        ║
║ D        ║
║ E        ║
║ F        ║
║ G        ║
╚══════════╝




更多信息:Visual Representation of SQL Joins

mysql - 合并两个表,排除相同的记录-LMLPHP

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL


不幸的是,MySQL不支持FULL OUTER JOIN,所以我使用了2个LEFT JOIN的并集。

mysql - 合并两个表,排除相同的记录-LMLPHP

mysql - 合并两个表,排除相同的记录-LMLPHP

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins中的所有图像

附录


  但是,如果我有两个具有不同列的不同表,但是它们都有一个相同的列,该怎么办?所用的SELECT语句的列数不同


您可以使用其他列轻松扩展它。

SELECT 'T1' AS tab_name, T1.col_name, T1.col1, NULL AS col2
FROM  T1
LEFT JOIN  T2
  ON T1.col_name=  T2.col_name
WHERE T2.col_name IS NULL
UNION
SELECT 'T2' AS tab_name, T2.col_name, NULL, T2.col2
FROM  T2
LEFT JOIN  T1
  ON T1.col_name=  T2.col_name
WHERE T1.col_name IS NULL;


LiveDemo

输出:

╔══════════╦══════════╦══════╦═════════════════════╗
║ tab_name ║ col_name ║ col1 ║        col2         ║
╠══════════╬══════════╬══════╬═════════════════════╣
║ T1       ║ C        ║    3 ║                     ║
║ T1       ║ D        ║    4 ║                     ║
║ T2       ║ E        ║      ║ 2016-01-03 00:00:00 ║
║ T2       ║ F        ║      ║ 2016-01-02 00:00:00 ║
║ T2       ║ G        ║      ║ 2016-01-01 00:00:00 ║
╚══════════╩══════════╩══════╩═════════════════════╝

关于mysql - 合并两个表,排除相同的记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34610639/

10-12 07:34
查看更多