我需要在一个表中并列放置两部分，它们具有相同的描述和不同的“外部ID”。输入表如下所示：
+--+-------+-----------+
|ID|OUTERID|DESCRIPTION|
+--+-------+-----------+
|1 |1 |some desc 1|
+--+-------+-----------+
|2 |1 |some desc 2|
+--+-------+-----------+
|3 |1 |some desc 3|
+--+-------+-----------+
|4 |2 |some desc 2|
+--+-------+-----------+
|5 |2 |some desc 3|
+--+-------+-----------+
这是我要从选择中实现的目标：
+-------------+-------------+
|DESCRIPTION_1|DESCRIPTION_2|
+-------------+-------------+
|some desc 1 |NULL |
+-------------+-------------+
|some desc 2 |some desc 2 |
+-------------+-------------+
|some desc 3 |some desc 3 |
+-------------+-------------+
我尝试了一个简单的JOIN，但是如您所见，我也需要显示不匹配的值。
SELECT a.DESCRIPTION AS DESCRIPTION_1,
b.DESCRIPTION AS DESCRIPTION_2
FROM My_Table a
JOIN My_Table b on a.DESCRIPTION = b.DESCRIPTION
WHERE a.OUTERID = 1
AND b.OUTERID = 2
最佳答案
如果我理解正确，则可以使用完全外部联接来执行此操作：
SELECT a.DESCRIPTION AS DESCRIPTION_1,
b.DESCRIPTION AS DESCRIPTION_2
FROM (SELECT a.*
FROM My_Table a
WHERE a.OUTERID = 1
) a FULL JOIN
(SELECT b.*
FROM My_Table b
WHERE b.OUTERID = 2
) b
ON a.DESCRIPTION = b.DESCRIPTION;