DB2 Warehouse on Cloud中如何将三个单行列表的列合并为一行？

嗨，既然你的三个表各自都只有一行数据，而且所有列名都是唯一的，那用CROSS JOIN就能完美解决你的问题！这应该是最直接的方式，完全符合你保留原列名、合并成一行的需求。

为什么之前的方法没生效？ #

先帮你理清一下之前尝试的方法为什么不符合预期：

• UNION/UNION ALL是纵向合并行的工具，它要求多个查询的列数、数据类型完全匹配，结果是把各行上下堆叠起来，而不是横向合并列，所以肯定达不到你要的效果。
• INNER JOIN如果没有指定关联条件，其实和CROSS JOIN等价，但如果加了错误的关联条件（比如不存在的列），就会返回空集或报错；不过用CROSS JOIN写法更明确，可读性更强，不容易踩坑。

通用解决方案 #

假设你的三个表分别是table_a、table_b、table_c，直接用CROSS JOIN关联，然后选择所有列即可：

SELECT a.*, b.*, c.*
FROM table_a a
CROSS JOIN table_b b
CROSS JOIN table_c c;

DB2也支持更简洁的隐式CROSS JOIN写法，效果完全一样：

SELECT a.*, b.*, c.*
FROM table_a a, table_b b, table_c c;

结合你的实际查询语句的示例 #

根据你给出的表创建逻辑，这里给你写一个具体的实现示例（假设另外两个表对应SYSTEM_NAME='POWER'和SYSTEM_NAME='HYDRO'的场景）：

SELECT fuel.*, power.*, hydro.*
FROM (
    -- 第一个表：FUEL统计
    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
) AS fuel
CROSS JOIN (
    -- 第二个表：POWER统计
    SELECT SUM(POWER_TEMP.POWER_MLAD_VALUE) AS POWER 
    FROM (
        SELECT ML_ANOMALY_DETECTION.MLAD_METRIC AS MLAD_METRIC, 
               ML_ANOMALY_DETECTION.MLAD_VALUE AS POWER_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 = 'POWER') 
        WHERE ML_ANOMALY_DETECTION.MLAD_METRIC = 'IFOREST_SCORE' 
          AND ML_ANOMALY_DETECTION.DATETIME >= (CURRENT DATE - 9 DAYS) 
        ORDER BY DATETIME DESC
    ) AS POWER_TEMP
) AS power
CROSS JOIN (
    -- 第三个表：HYDRO统计
    SELECT SUM(HYDRO_TEMP.HYDRO_MLAD_VALUE) AS HYDRO 
    FROM (
        SELECT ML_ANOMALY_DETECTION.MLAD_METRIC AS MLAD_METRIC, 
               ML_ANOMALY_DETECTION.MLAD_VALUE AS HYDRO_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 = 'HYDRO') 
        WHERE ML_ANOMALY_DETECTION.MLAD_METRIC = 'IFOREST_SCORE' 
          AND ML_ANOMALY_DETECTION.DATETIME >= (CURRENT DATE - 9 DAYS) 
        ORDER BY DATETIME DESC
    ) AS HYDRO_TEMP
) AS hydro;

因为每个子查询都只返回一行数据，CROSS JOIN之后只会生成一行结果，包含三个子查询的所有原列名，完全符合你的需求。

内容的提问来源于stack exchange，提问作者danielo