关于如何在 SQL 中使用 JOIN、子查询和 UNION 组合数据的案例研究

这是各种表和匹配挑战的整体视图，即根据 Status 的值“有条件地匹配”四个“就绪”表之一。

获取状态名称非常简单。为此，我们可以使用 INNER JOIN；然而，显示原因名称则更难。

一旦你认真思考，你就会意识到答案取决于更好地理解各种“原因”表。如果它们可以被统一处理，那么我们就可以简单地一次性连接它们，避免重复条目，从而解决问题。

当我最初考虑这个问题时，我曾想过是否可以使用 SELECT CASE 来解决问题，然后我突然想到！我们可以使用 UNIONS 来完成。如果你注意到，所有表的结构都非常相似。

鉴于此，我们可以创建一个派生表，它是四个“原因”表的组合结果，然后可以使用状态代码和就绪代码将其连接到原始表。这避免了必须“有条件地匹配”到四个表中的每一个。

在我看来，答案分为三个不同的子解决方案

1. 连接到 Status 表以获取状态名称。
2. 使用 UNION 组合各种“原因”表；从而避免“条件匹配”问题。
3. 通过子查询（派生表）将第二步的结果合并到最终查询中。

让我们按顺序解决这些子问题。

1. 使用 INNER JOIN 返回 StatusName

由于我们需要从状态表中获取与事件中的状态代码对应的 StatusName，因此我们可以使用 INNER JOIN。您可能还记得，Inner join 返回满足 join 条件的行。这是最常见的 join。

在最常见的示例中，当主键与外键匹配时，只返回匹配键的行组合。

这正是您所拥有的。StatusCode 是状态表的主键，我们可以用它来匹配 Event 中的外键值 StatusCode。

由于状态代码在 status 表中是唯一的，因此您不必担心 join 会返回多行。

下图总结了正在进行的操作

这是用于获取状态名称的 SQL 语句，我用红色标出了 INNER JOIN 部分。

SELECT EventID,
       Duration,
       S.Name
FROM Event E
       INNER JOIN Status S
       ON E.StatusCode = S.StatusCode

当然，还有更多工作要做，我们还需要获取原因名称，但这是一个好的开始。

2. 利用 UNION 组合原因表行

UNION 称为集合运算符。UNION 运算符用于将多个表中的行组合成一个结果。join 用于将不同表中的列组合成一行，而 UNION 运算符则将每个表中的行添加在一起。

在下图中，您可以看到四个独立的“原因”表。

通过成功应用 union 子句，我们将组合所有行。您还会注意到我们正在添加一个与状态代码对应的静态列。静态列只是一个花哨的说法，表示我们正在添加一个固定值的列。

通过这样做，我们可以将每个表的行与正确的状态代码关联起来。请记住，这是我们的一大障碍；我们没有一个好的方法可以根据状态代码有条件地匹配到四个表中的每一个。

通过将状态代码添加到结果中，我们最终可以使用状态和原因代码匹配到正确的行。

这是用于创建统一结果的代码。

SELECT 'R', ReasonCode, Name
FROM ReadyReason
UNION
SELECT 'D1', ReasonCode, Name
FROM DelayReason
UNION
SELECT 'S', ReasonCode, Name
FROM SpareReason
UNION
SELECT 'D2', ReasonCode, Name
FROM DownReason

3. 使用子查询将派生表包含在最终结果中

一旦创建了 union，我们就可以使用它来匹配和拉取原因名称。正如您在下图中看到的，匹配变得容易得多。我们不再需要检查状态代码，然后决定使用四个表中的哪一个，然后再根据原因代码进行匹配以获取名称。

相反，我们现在可以使用标准的 INNER JOIN 将状态代码和原因代码匹配到 union 的结果。

但这确实引出了一个问题。我们如何将该结果引入到我们的查询中？

幸运的是，有一种方法可以使用子查询来完成。子查询只是在另一个查询中定义的查询。我们可以在很多地方使用子查询，例如在 SELECT 或 WHERE 子句中。我们也可以在 FROM 子句中使用它们。当在这里使用时，它们通常指的是派生表。我们 union 的结果就是一个派生表。

派生表用括号括起来，就像子查询一样，但它们也给定一个名称。

如果您仔细查看 SQL，您会看到 UNION 结果被命名为 SR。

您还会注意到 static 字段有一个列别名。这样我们就可以在 join 条件中引用它。这使得 SQL 更易于阅读和理解。

在下面的示例中，我已将 UNION 涂成绿色，并将其在 INNER JOIN 中的使用涂成蓝色。

SELECT EventID,
       Duration,
       SR.ReasonName
FROM Event E
       INNER JOIN
       (
        SELECT 'R' as StatusCode, ReasonCode, Name
        FROM ReadyReason
        UNION
        SELECT 'D1', ReasonCode, Name
        FROM DelayReason
        UNION
        SELECT 'S', ReasonCode, Name
        FROM SpareReason
        UNION
        SELECT 'D2', ReasonCode, Name
        FROM DownReason
       ) SR
       ON E.StatusCode = SR.StatusCode AND
       E.ReasonCode = SR.ReasonCode

请记住 inner join 的形式是

INNER JOIN {table} ON {match conditions}

我们所做的就是用我们的派生表替换 {table}。

在这种情况下，它运作得非常好。

最终查询

为了创建最终结果，我们将三个子解决方案组合在一起。从上面的部分可以看出，每种方法都相对简单。当然，有一些语法需要处理，但我认为总的来说，这些想法都很直接。

当然，当它被堆积起来时，它会看起来更难，更令人望而生畏，但希望您已经看到，如果您采取循序渐进的方法来构建您的 SQL，那么它就会变得不那么困难。

这是最终 SQL 的文本，已进行颜色编码以突出显示每个子解决方案。

第一个 INNER JOIN 是红色，UNION 是绿色，使用派生表的 INNER JOIN 是蓝色。

SELECT EventID,
       Duration,
       S.Name as [Status Name],
       SR.ReasonName as [Reason Name]
FROM Event E
       INNER JOIN Status S
       ON E.StatusCode = S.StatusCode
       INNER JOIN
       (
        SELECT 'R', ReasonCode, Name
        FROM ReadyReason
        UNION
        SELECT 'D1', ReasonCode, Name
        FROM DelayReason
        UNION
        SELECT 'S', ReasonCode, Name
        FROM SpareReason
        UNION
        SELECT 'D2', ReasonCode, Name
        FROM DownReason
       ) SR
       ON E.StatusCode = SR.StatusCode AND
       E.ReasonCode = SR.ReasonCode

本次案例研究到此结束。

我希望您觉得这有帮助。您是否看到，尽管最终结果看起来很复杂，但它实际上是由三个更简单的概念组成的？

我的许多学生都对这个过程提出疑问，即如何编写像这样复杂的查询。

例如，他们不确定从何开始。这就是我可以提供帮助的地方。我有一些非常好的想法，可以帮助您自信地开始，以便您在第一天就可以开始编写查询。

其他人想知道如何将所有这些组合在一起。正如您从案例研究中可以看到的，有一个循序渐进的过程。我喜欢循序渐进地处理事情。

在解决复杂问题时，我喜欢在成功的基础上再接再厉。您是否看到我如何从一个简单的解决方案开始，添加一个步骤，然后继续下去？

如果您喜欢我的教学风格，我建议您订阅下面的我的时事通讯，这样您就不会错过任何未来的培训机会！