代码之家 › 专栏 › 技术社区 › danielo

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

ibm-cloud db2 sql

danielo · 技术社区 · 7 年前

我是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

我已经尝试了JOIN、Internal JOIN、UNION/UNION ALL,但无法使其正常工作。我该怎么做?

2 回复 | 直到 7 年前

jmarkmurphy 7 年前

使用如下交叉连接:

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行。

Crezzer7 7 年前

使用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

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