时间戳

首页 > 解决方案 > 如何基于一列连接多个表的结果（通过 UNION ALL）

sql - 如何基于一列连接多个表的结果（通过 UNION ALL）

问题描述

好吧，我编写的 SQL 语句运行良好，但我想让它不那么笨重，并且只使用一个“ACCEPTANCE_DATE”，因为它在所有表中都是相同的。

我正在尝试使用 UNION ALL 语句加入多个表的结果。下面的例子工作得很好。

SEL COUNT(*)FROM
MY_DATABASE.HUMAN_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.FINANCIAL_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.INFRASTRUCTURE_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09';

所有表都有相同类型的列，例如每个表都有一个名为“ACCEPTANCE_DATE”的列。我得到的结果是正确的。尽管如此，我在一个查询中组合了很多表（使用 UNION ALL）并且想知道是否有办法转换这个查询，所以我不必在每个选择语句中更新 ACCEPTANCE_DATE='2015-08-09' . 理想情况下，我只想定义一次，尤其是当我使用超过 30 个 UNION ALL 子句时，例如

SEL * FROM
    (SEL COUNT(*)FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09';

下面的语句获取数据。

SEL COUNT(*)FROM
MY_DATABASE.HUMAN_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.FINANCIAL_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09'
UNION ALL
SEL COUNT(*)FROM
MY_DATABASE.INFRASTRUCTURE_RESOURCES
WHERE ACCEPTANCE_DATE='2015-08-09';

如上所述，我希望它类似于以下内容：

SEL * FROM
    (SEL COUNT(*)FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL COUNT(*)FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09';

标签： sqlteradataunion-all

解决方案

也许这就是您正在寻找的：

SEL COUNT(*) FROM
    (SEL ACCEPTANCE_DATE, 1 AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 2 AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 3 AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ORIGIN;

您甚至可以给 ORIGIN 一些更有意义的名称并在之后显示它们：

SEL ORIGIN, COUNT(*) FROM
    (SEL ACCEPTANCE_DATE, 'HUMAN' AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'FINANCIAL' AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'INFRASTRUCTURE' AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ORIGIN;

这导致两列。现在，它仍然没有解决第三个值不显示的问题，但是这样您就知道哪些值丢失了，并且可以轻松区分哪些应该是零。如果这对您来说还不够，那么代码会变得更糟糕。以后可能会想办法解决。

为了解决空表丢失条目的问题，我想到了两种可能的解决方案。选择取决于操作是否只执行一次，或者这是重复发生的操作还是仅执行一次事件。如果您计划多次执行此操作，那么在数据库上创建一个包含所有原始表名称（或一些快捷方式，您会明白的）的表可能是个好主意。对于这个最小的例子，让我们考虑这样一个存在于名称 SOURCE_TABLES 下的东西：

SELECT RESOURCE FROM SOURCE_TABLES
/*
    RESOURCE:
    HUMAN
    FINANCIAL
    INFRASTRUCTURE
*/

在这种情况下，之前提供的脚本只需要稍作修改：

SEL ST.RESOURCE, COUNT(T1.ACCEPTANCE_DATE) FROM SOURCE_TABLES ST
    LEFT JOIN (SEL ACCEPTANCE_DATE, 'HUMAN' AS ORIGIN FROM
    MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'FINANCIAL' AS ORIGIN FROM
    MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
    SEL ACCEPTANCE_DATE, 'INFRASTRUCTURE' AS ORIGIN FROM
    MY_DATABASE.INFRASTRUCTURE_RESOURCES) AS T1
    ON ST.RESOURCE = T1.ORIGIN
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY ST.RESOURCE;

在这里，通过使用 LEFT JOIN，您可以确保表中的每个条目都存在于输出中，即使在 T1 中没有具有指定来源的行。COUNT(T1.ACCEPTANCE_DATE) 利用了 NULL 不累加到计数器的事实。

现在，如果出于某种原因您不喜欢创建表的想法（您不能在数据库上创建对象或者单个操作太麻烦），您可以坚持使用更容易的数字的想法即时生成。下面的解决方案利用了与上面相同的想法，但在读取的表数量方面更加灵活，并且显然不需要您创建额外的表。考虑到您提到了 30 张桌子，这可能是一个更好的选择。有人可以争辩说它的可读性较差：

WITH numbers AS (
SEL 1 AS number
UNION ALL
SEL number + 1 FROM numbers WHERE number + 1 <= 3 -- Change 3 to the number of sourcing tables
), input_merged AS ( -- if we already use the WITH clause we can do so for merging input. It's more readable
SEL ACCEPTANCE_DATE, 1 AS ORIGIN FROM MY_DATABASE.HUMAN_RESOURCES
    UNION ALL
SEL ACCEPTANCE_DATE, 2 AS ORIGIN FROM MY_DATABASE.FINANCIAL_RESOURCES
    UNION ALL
SEL ACCEPTANCE_DATE, 3 AS ORIGIN FROM MY_DATABASE.INFRASTRUCTURE_RESOURCES
-- add further sources accordingly...
)
SEL COUNT(ACCEPTANCE_DATE) FROM numbers n
    LEFT JOIN input_merged im ON n.number = im.ORIGIN
WHERE ACCEPTANCE_DATE='2015-08-09'
GROUP BY n.number;

这应该会产生第一个询问和期望的输出。

至于 WITH 语句中的数字部分，您可能需要参考this，请注意，在此解决方案中，我使用 WITH 也像 Christoph 那样合并输入。如果您使用 ORACLE 数据库，则使用 CONNECT BY LEVEL 可能是创建数字序列的更好选择。

希望现在你可以实现你想要的！

推荐阅读