Oracle中如何将三个域表的交易短描述合并至单列并去重？

核心思路 #

由于每个TRANS_ID仅存在于一个域表中，我们可以先对每个域表单独做去重（提取每个TRANS_ID的唯一描述），再将三个域表的去重结果合并，最后与交易表关联，即可得到每个交易ID一行、描述列统一的结果。

解决方案SQL #

方式一：使用CTE（公共表表达式）

WITH dmn_desc AS (
    -- 处理第一个域表，取每个TRANS_ID的第一条描述
    SELECT TRANS_ID, DMN1_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT 
            TRANS_ID, 
            DMN1_SHORT_DESC,
            ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL1
    ) t
    WHERE row_num = 1
    
    UNION ALL
    
    -- 处理第二个域表
    SELECT TRANS_ID, DMN2_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT 
            TRANS_ID, 
            DMN2_SHORT_DESC,
            ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL2
    ) t
    WHERE row_num = 1
    
    UNION ALL
    
    -- 处理第三个域表
    SELECT TRANS_ID, DMN3_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT 
            TRANS_ID, 
            DMN3_SHORT_DESC,
            ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL3
    ) t
    WHERE row_num = 1
)
-- 关联交易表获取最终结果
SELECT 
    trns.TRANS_ID,
    COALESCE(dmn.TRANS_SHORT_DESC, '') AS TRANS_SHORT_DESC, -- 无描述时显示空字符串，可按需调整
    trns.TRANS_START_TM,
    trns.TRANS_END_TM
FROM TRANS_TBL trns
LEFT JOIN dmn_desc dmn ON trns.TRANS_ID = dmn.TRANS_ID;

方式二：直接使用子查询

如果你的数据库不支持CTE，也可以用子查询实现：

SELECT 
    trns.TRANS_ID,
    COALESCE(dmn.TRANS_SHORT_DESC, '') AS TRANS_SHORT_DESC,
    trns.TRANS_START_TM,
    trns.TRANS_END_TM
FROM TRANS_TBL trns
LEFT JOIN (
    SELECT TRANS_ID, DMN1_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT TRANS_ID, DMN1_SHORT_DESC, ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL1
    ) t WHERE row_num=1
    UNION ALL
    SELECT TRANS_ID, DMN2_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT TRANS_ID, DMN2_SHORT_DESC, ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL2
    ) t WHERE row_num=1
    UNION ALL
    SELECT TRANS_ID, DMN3_SHORT_DESC AS TRANS_SHORT_DESC
    FROM (
        SELECT TRANS_ID, DMN3_SHORT_DESC, ROW_NUMBER() OVER(PARTITION BY TRANS_ID ORDER BY TRANS_ID) AS row_num
        FROM DMN_TANS_DESC_TBL3
    ) t WHERE row_num=1
) dmn ON trns.TRANS_ID = dmn.TRANS_ID;

关键说明 #

1. 去重逻辑：每个域表内用ROW_NUMBER()按TRANS_ID分区，取第一条记录，解决单域表内同一交易ID对应多条描述的问题。如果业务需要指定取某条描述（比如最新创建的），可以把ORDER BY TRANS_ID换成对应的排序字段（如描述的创建时间）。
2. 合并域表：用UNION ALL而非UNION，因为每个TRANS_ID仅存在于一个域表，不会产生重复数据，UNION ALL性能更优。
3. 空值处理：COALESCE函数用于处理无对应描述的交易记录，可根据需求替换为NULL或其他默认值。

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