如何对多表关联构建的无命名临时结果集的两列进行乘法计算

临时结果集命名规则说明 #

临时构建的查询结果集（派生表/子查询）没有系统默认名称，必须手动指定别名后才能在外部查询中引用。不过你当前的需求不需要额外嵌套一层查询，可以直接在现有SELECT语句中新增计算列实现需求。

新增乘法计算列的实现方案 #

由于RATE和BASIS_TYPE都属于你已经关联的A表（REPLDOADM.IRE_ESTIMATE_TRANS_MAP），直接在SELECT字段列表中新增乘法计算逻辑即可，修改后的完整SQL如下：

select A.*,
    A.TRANSACTION_ID,
    A.RATE * A.BASIS_TYPE AS CALCULATED_VALUE, -- 新增的计算列，别名可自行修改
    -- 如果需要规避空值导致计算结果为空，可改用如下写法
    -- COALESCE(A.RATE, 0) * COALESCE(A.BASIS_TYPE, 0) AS CALCULATED_VALUE,
    B.AMOUNT,
    B.CHARGE_ID,
    C.CHARGE_TYPE_ID,
    D.CHARGE_GROUP,
    E.EXCHANGE_ID,
    E.TRADE_DATE,
    timestamp_table.timeformated
FROM REPLDOADM.IRE_ESTIMATE_TRANS_MAP A
left join REPLDOADM.IRE_CHARGES_ESTIMATE B ON A.ESTIMATE_ID = B.ESTIMATE_ID
left join REPLDOADM.IRE_CHARGES_LU C ON B.CHARGE_ID = C.CHARGE_ID
left join REPLDOADM.ire_charge_types_lU D ON C.CHARGE_TYPE_ID = D.CHARGE_TYPE_ID
left join REPLDOADM.VW_IRE_TRADE_TRANSACTIONS E ON A.TRANSACTION_ID = E.TRANS_ID
left join (
select to_char(create_ts, 'HH24:MI:SS') as timeformated, TRANS_ID
from REPLDOADM.VW_IRE_TRADE_TRANSACTIONS) timestamp_table ON (A.TRANSACTION_ID = 
timestamp_table.TRANS_ID)

补充：将整个结果集作为临时表使用的实现方式 #

如果后续有需求需要对整个关联后的结果集做二次计算，可以用以下两种常用方式给结果集命名：

• 方式1：派生表加别名

SELECT *, RATE * BASIS_TYPE AS CALCULATED_VALUE
FROM (
    -- 这里放你原来的完整关联查询
    select A.*,
        A.TRANSACTION_ID,
        B.AMOUNT,
        B.CHARGE_ID,
        C.CHARGE_TYPE_ID,
        D.CHARGE_GROUP,
        E.EXCHANGE_ID,
        E.TRADE_DATE,
        timestamp_table.timeformated
    FROM REPLDOADM.IRE_ESTIMATE_TRANS_MAP A
    left join REPLDOADM.IRE_CHARGES_ESTIMATE B ON A.ESTIMATE_ID = B.ESTIMATE_ID
    left join REPLDOADM.IRE_CHARGES_LU C ON B.CHARGE_ID = C.CHARGE_ID
    left join REPLDOADM.ire_charge_types_lU D ON C.CHARGE_TYPE_ID = D.CHARGE_TYPE_ID
    left join REPLDOADM.VW_IRE_TRADE_TRANSACTIONS E ON A.TRANSACTION_ID = E.TRANS_ID
    left join (
    select to_char(create_ts, 'HH24:MI:SS') as timeformated, TRANS_ID
    from REPLDOADM.VW_IRE_TRADE_TRANSACTIONS) timestamp_table ON (A.TRANSACTION_ID = 
    timestamp_table.TRANS_ID)
) AS temp_result -- 手动指定临时结果集别名

• 方式2：CTE（公用表表达式）

WITH temp_result AS (
    -- 这里放你原来的完整关联查询
    select A.*,
        A.TRANSACTION_ID,
        B.AMOUNT,
        B.CHARGE_ID,
        C.CHARGE_TYPE_ID,
        D.CHARGE_GROUP,
        E.EXCHANGE_ID,
        E.TRADE_DATE,
        timestamp_table.timeformated
    FROM REPLDOADM.IRE_ESTIMATE_TRANS_MAP A
    left join REPLDOADM.IRE_CHARGES_ESTIMATE B ON A.ESTIMATE_ID = B.ESTIMATE_ID
    left join REPLDOADM.IRE_CHARGES_LU C ON B.CHARGE_ID = C.CHARGE_ID
    left join REPLDOADM.ire_charge_types_lU D ON C.CHARGE_TYPE_ID = D.CHARGE_TYPE_ID
    left join REPLDOADM.VW_IRE_TRADE_TRANSACTIONS E ON A.TRANSACTION_ID = E.TRANS_ID
    left join (
    select to_char(create_ts, 'HH24:MI:SS') as timeformated, TRANS_ID
    from REPLDOADM.VW_IRE_TRADE_TRANSACTIONS) timestamp_table ON (A.TRANSACTION_ID = 
    timestamp_table.TRANS_ID)
)
SELECT *, RATE * BASIS_TYPE AS CALCULATED_VALUE FROM temp_result;

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