SQL Full Out Join在同一表的同一列上 [英] SQL Full Outer Join on same column in same table

问题描述

这可能是更多的设计问题，但我希望没有太多的伏都教徒是有可能的.

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

我想得到这样的东西(ID在列中分组):

And I want to get something like this (with the ID's grouped in columns):

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

我也尝试过在JOIN ON和WHERE上玩，但是似乎无法获得预期的结果.

I've also tried playing around with the JOIN ON and WHERE but can't seem to get the desired result.

我在网上可以找到的最接近的问题是 this一个，但我也无法让UNION正常工作.

The closest question I could find online is this one, but I couldn't get UNION to work either.

性能不是问题，我可以假设只有2组.

Performance isn't an issue, and I can assume there are only 2 groups.

有什么想法吗?

推荐答案

您首先做错了事，然后尝试对其进行修复.那是行不通的.

You're doing the wrong thing first, and attempting to fix it up afterwards. That's not going to work.

您要加入的内容是select * from stuff where grp = 'a'和select * from stuff where grp = 'b'.因此，加入这些人:

The things you want to join are select * from stuff where grp = 'a' and select * from stuff where grp = 'b'. So join those:

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

SQL提琴

这篇关于SQL Full Out Join在同一表的同一列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！