将不同表中的三列合并为一行

Question

我是 sql 的新手，正在尝试将来自三个不同表的列值合并到云上的 DB2 仓库中的一行中。每个表仅包含一行和唯一的列名。所以我想要的只是将这三个连接到一行，它们的原始列名。

每个表都是从如下所示的语句构建的：

SELECT SUM(FUEL_TEMP.FUEL_MLAD_VALUE) AS FUEL 
FROM
    (SELECT ML_ANOMALY_DETECTION.MLAD_METRIC AS MLAD_METRIC, ML_ANOMALY_DETECTION.MLAD_VALUE AS FUEL_MLAD_VALUE, ML_ANOMALY_DETECTION.TAG_NAME AS TAG_NAME, ML_ANOMALY_DETECTION.DATETIME AS DATETIME, DATA_CONFIG.SYSTEM_NAME AS SYSTEM_NAME
     FROM ML_ANOMALY_DETECTION 
         INNER JOIN DATA_CONFIG ON 
               (ML_ANOMALY_DETECTION.TAG_NAME  =DATA_CONFIG.TAG_NAME AND 
                DATA_CONFIG.SYSTEM_NAME = 'FUEL') 
     WHERE ML_ANOMALY_DETECTION.MLAD_METRIC = 'IFOREST_SCORE'
       AND ML_ANOMALY_DETECTION.DATETIME >= (CURRENT DATE - 9 DAYS) 
     ORDER BY DATETIME DESC)
AS FUEL_TEMP

我已经尝试过加入，内部连接，联合/联合所有，但无法让它正常工作。我该怎么做？

Answer 1

使用如下所示的交叉联接：

create table table1 (field1 char(10));
create table table2 (field2 char(10));
create table table3 (field3 char(10));
insert into table1 values('value1');
insert into table2 values('value2');
insert into table3 values('value3');
select *
  from table1
  cross join table2
  cross join table3;

结果：

field1     field2     field3
---------- ---------- ----------
value1     value2     value3

交叉联接将左侧的所有行与右侧的所有行连接起来。您最终将得到行的乘积(表 1 行 x 表2 行 x 表3 行(。由于每个表只有一行，因此您将获得 (1 x 1 x 1( = 1 行。

Answer 2

使用 UNION 应该可以解决您的问题。像这样：

SELECT
  WarehouseDB1.WarehouseID AS TheID,
  'A' AS TheSystem,
  WarehouseDB1.TheValue AS TheValue
FROM WarehouseDB1
UNION
SELECT
  WarehouseDB2.WarehouseID AS TheID,
  'B' AS TheSystem,
  WarehouseDB2.TheValue AS TheValue
FROM WarehouseDB2
UNION
  WarehouseDB3.WarehouseID AS TheID,
  'C' AS TheSystem,
  WarehouseDB3.TheValue AS TheValue
FROM WarehouseDB3

如果您告诉我它们是什么，请用您的表名和行调整代码。此类查询将返回如下所示的内容：

TheID   TheSystem   TheValue
  1         A          10
  2         A          20
  3         B          30
  4         C          40
  5         C          50

只要列名在每个查询中匹配，就应该得到所需的结果。