oracle 同一表中同一列的 SQL 完全外部联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16966930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Full Outer Join on same column in same table
提问by roadkill
This may be more of a design issue than anything, but I'm hoping it's possible without too much voodoo.
这可能更像是一个设计问题，但我希望没有太多巫毒教是可能的。
Suppose I have a table like this:
假设我有一张这样的表：
SELECT * FROM stuff;
id grp
1 a
2 a
3 a
1 b
2 b
4 b
And I want to get something like this (with the ID's grouped in columns):
我想得到这样的东西（ID 分组在列中）：
a.id b.id
1 1
2 2
3 null
null 4
Is this possible? I've tried the following query...
这可能吗？我试过以下查询...
SELECT a.id, b.id FROM stuff a
FULL OUTER JOIN stuff b ON a.id = b.id
WHERE a.grp = 'a' AND b.grp = 'b';
... but I only get the common nodes:
...但我只得到公共节点：
a.id b.id
1 1
2 2
I've also tried playing around with the JOIN ON and WHERE but can't seem to get the desired result.
我也尝试过使用 JOIN ON 和 WHERE 但似乎无法获得所需的结果。
The closest question I could find online is this one, but I couldn't get UNION to work either.
我可以在网上找到的最接近的问题是this one，但我也无法让 UNION 工作。
Performance isn't an issue, and I can assume there are only 2 groups.
性能不是问题，我可以假设只有 2 个组。
Any ideas?
有任何想法吗？
回答by
You're doing the wrong thing first, and attempting to fix it up afterwards. That's not going to work.
你首先做错了事，然后试图修复它。那是行不通的。
The things you want to join are select * from stuff where grp = 'a'and select * from stuff where grp = 'b'. So join those:
你想加入的东西是select * from stuff where grp = 'a'和select * from stuff where grp = 'b'。所以加入那些：
select a.ID as a, b.ID as b from
(select * from stuff where grp = 'a') a
full join
(select * from stuff where grp = 'b') b
on b.id = a.id
