SQL:将表与自身联接并在结果中允许 null

Question

>我有这样的表格：

+----+------+-------+
| ID | Type | Value |
+----+------+-------+
| 1  | A    | 111   |
+----+------+-------+
| 2  | B    | 222   |
+----+------+-------+
| 1  | B    | 333   |
+----+------+-------+
| 3  | A    | 444   |
+----+------+-------+
| 1  | C    | 555   |
+----+------+-------+

ID不是唯一的，但与Type一起ID创建一个唯一的键。我希望它返回带有 ID:1 的 Type:A 和 Type:B 的值。这是我使用的代码：

select tA.ID, tA.Value as ValueA, tB.Value as ValueB
from Table_1 tA 
join Table_1 tB on tA.ID = tB.ID and (tA.Type = 'A' and tB.Type = 'B')
where tA.ID = 1

这将返回正确的结果：

+----+--------+--------+
| ID | ValueA | ValueB |
+----+--------+--------+
| 1  | 111    | 333    |
+----+--------+--------+

但是如果它缺少ID:1的Type:B，就像这样：

+----+------+-------+
| ID | Type | Value |
+----+------+-------+
| 1  | A    | 111   |
+----+------+-------+
| 2  | B    | 222   |
+----+------+-------+
| 3  | A    | 444   |
+----+------+-------+
| 1  | C    | 555   |
+----+------+-------+

。我运行与上面相同的代码，它返回一个空结果。

如果缺少Type:B，我希望它返回以下结果：

+----+--------+--------+
| ID | ValueA | ValueB |
+----+--------+--------+
| 1  | 111    | NULL   |
+----+--------+--------+

或者，如果它缺少Type:A但有Type:B，那么结果应该是：

+----+--------+--------+
| ID | ValueA | ValueB |
+----+--------+--------+
| 1  | NULL   | 333    |
+----+--------+--------+

如果 Type：A 和 Type：B 都丢失，那么它们是否返回空或包含 NULL 并不重要，如下所示：

+----+--------+--------+
| ID | ValueA | ValueB |
+----+--------+--------+
| 1  | NULL   | NULL   |
+----+--------+--------+

我尝试使用full outer join：

select tA.ID, tA.Value as ValueA, tB.Value as ValueB
from Table_1 tA 
full outer join Table_1 tB on tA.ID = tB.ID and (tA.Type = 'A' and tB.Type = 'B')
where tA.ID = 1

。但它返回的结果包括 Type:C 的值为 ValueA ：

+----+--------+--------+
| ID | ValueA | ValueB |
+----+--------+--------+
| 1  | 111    | NULL   |
+----+--------+--------+
| 1  | 555    | NULL   |
+----+--------+--------+

如何修复我的代码以返回一个结果，该结果将包含缺失Type的 null 值？

Answer 1

由于 ID 和 Type 对是唯一的，因此您可以group by id和使用条件聚合：

select
  id,
  max(case type when 'A' then value end) ValueA,
  max(case type when 'B' then value end) ValueB
from Table_1 
where id = 1 and type in ('A', 'B')
group by id 

请参阅演示。
结果：

| id  | ValueA | ValueB |
| --- | ------ | ------ |
| 1   | 111    | 333    |

或：

| id  | ValueA | ValueB |
| --- | ------ | ------ |
| 1   | 111    |        |

或：

| id  | ValueA | ValueB |
| --- | ------ | ------ |
| 1   |        | 333    |

对于不同的情况。

Answer 2

您可以使用 full join ，但过滤很棘手。 这将起作用：

select tA.ID, tA.Value as ValueA, tB.Value as ValueB
from (select tA.*
      from Table_1 tA
      where tA.ID = 1 and tA.Type = 'A'
     ) tA full join
     (select tB.*
      from Table_2 tB
      where tB.ID = 1 and tB.Type = 'B'
     ) tB
     on tA.ID = tB.ID ;

我认为这也有效：

select tA.ID, tA.Value as ValueA, tB.Value as ValueB
from Table_1 tA full join
     Table_1 tB 
     on tA.ID = tB.ID
where (tA.ID = 1 or tB.ID = 1) and
      (tA.Type = 'A' or tA.Type is null) and
      (tA.Type = 'B' or tB.Type is null);