我需要使用哪种连接来从多个表中创建一个表? [英] What kind of join do I need to use to create one table from many?

问题描述

我有 3 个表 A、B 和 C.每个表都有一个 ID 和一个值字段.我使用哪种连接将这些合并到一个表中，其中包含一个 ID 字段和列值 A、值 B、值 C?

I have 3 tables A, B and C. Each table has an ID and a value field. What kind of join do I use to consolidate these into one table with an ID field and columns value A, value B, value C?

当然，我只希望结果表中的每个 ID 有 1 条记录.谢谢.

Naturally, I want only 1 record for each ID in the result table. Thanks.

示例:

表A:

1 x
2 y

表 B:

2 a
3 b

表 C:

2 m

我需要的结果:

1 x - -
2 y a m
3 - b -

推荐答案

如果 Id 分布在所有表中，请将它们合并在一起并对其进行分组/聚合:

If the Ids are distributed across all the tables, union them together and group/aggregate on that:

SELECT Id, MAX(ValueA) AS ValueA, MAX(ValueB) AS ValueB, MAX(ValueC) AS ValueC
FROM (
    SELECT Id, ValueA, NULL AS ValueB, NULL AS ValueC
    FROM TableA
    UNION ALL
    SELECT Id, NULL AS ValueA, ValueB, NULL AS ValueC
    FROM TableB
    UNION ALL
    SELECT Id, NULL AS ValueA, NULL AS ValueB, ValueC
    FROM TableC
)
GROUP BY Id

如果每个 Id 都是唯一的并且在每个表中都存在一次:

If each Id is unique and exists once within each table:

SELECT TableA.Id, TableA.ValueA, TableB.ValueB, TableC.ValueC
FROM TableA
INNER JOIN TableB ON TableA.Id = TableB.Id
INNER JOIN TableC ON TableA.Id = TableC.Id

如果每个 Id 都是唯一的，存在于 TableA 中并且在其他表中出现一次或不存在(不存在的值作为 NULLs 提供):

If each Id is unique, exists in TableA and is present either once or doesn't exist in the other tables (non-existant values get supplied as NULLs):

SELECT TableA.Id, TableA.ValueA, TableB.ValueB, TableC.ValueC
FROM TableA
LEFT OUTER JOIN TableB ON TableA.Id = TableB.Id
LEFT OUTER JOIN TableC ON TableA.Id = TableC.Id

如果每个 Id 在 TableA 中唯一存在，但在其他表中可能多次存在，请使用某种聚合，例如 MAX 或 SUM:

If each Id exists uniquely in TableA but may exist multiple times in the other tables, use some sort of aggregation, like MAX or SUM:

SELECT TableA.Id, MAX(TableA.ValueA) AS ValueA, SUM(TableB.ValueB) AS ValueB, MIN(TableC.ValueC) AS ValueC
FROM TableA
INNER JOIN TableB ON TableA.Id = TableB.Id
INNER JOIN TableC ON TableA.Id = TableC.Id
GROUP BY TableA.Id

这篇关于我需要使用哪种连接来从多个表中创建一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！