Oracle SQL中如何关联三表并按参数表映射生成预订详情

核心需求实现方案 #

我们需要关联三张表，基于参数表的分类字段映射规则，将预订表的对应字段值拼接成格式化的Booking Details字段。以下是两种可行的实现方式：

方式一：静态SQL（适用于字段映射相对固定的场景）

SELECT
    trd.REQ_ID,
    trd.REQ_CASE_CATEGORY_CODE,
    -- 按参数表定义拼接格式化字符串
    LISTAGG(
        tbc.BCM_DISPLAY_NAME || ': ' || 
        CASE tbc.BCM_COLUMN_NAME
            WHEN 'BOOKING_DATE' THEN TO_CHAR(tbd.BOOKING_DATE, 'YYYY-MM-DD')
            WHEN 'PASSENGER_NAME' THEN tbd.PASSENGER_NAME
            WHEN 'FLIGHT_NUMBER' THEN tbd.FLIGHT_NUMBER
            WHEN 'SEAT_CLASS' THEN tbd.SEAT_CLASS
            -- 根据参数表的字段映射继续添加分支
        END,
        '; '
    ) WITHIN GROUP (ORDER BY tbc.BCM_SORT_ORDER) AS FORMATTED_BOOKING_DETAILS
FROM
    Tls_Request_Details trd
INNER JOIN
    tls_booking_details tbd 
        ON trd.REQ_ID = tbd.BOOKING_REQ_ID
INNER JOIN
    Ttls_booking_column_mapping tbc 
        ON trd.REQ_CASE_CATEGORY_CODE = tbc.BCM_CASE_CATEGORY_CODE
GROUP BY
    trd.REQ_ID, trd.REQ_CASE_CATEGORY_CODE;

关键说明：

• 关联逻辑：通过请求表的请求ID关联预订表，通过请求表的分类编码匹配参数表的映射规则，确保只获取当前请求分类对应的字段。
• 字段匹配：使用CASE语句将参数表的BCM_COLUMN_NAME与预订表的实际字段一一对应，日期类型可通过TO_CHAR做格式化处理。
• 字符串拼接：LISTAGG函数将每个字段的显示名称与值用指定分隔符拼接，WITHIN GROUP (ORDER BY tbc.BCM_SORT_ORDER)保证拼接顺序符合参数表定义的排序规则（需参数表包含BCM_SORT_ORDER字段控制顺序）。

方式二：动态SQL（适用于字段映射频繁变动的场景）

如果参数表的字段映射经常更新，静态SQL需要频繁修改，此时可以使用动态SQL自动生成字段匹配逻辑：

DECLARE
    v_target_category VARCHAR2(100) := 'FLIGHT_BOOKING'; -- 替换为目标分类编码
    v_case_logic CLOB;
    v_full_sql CLOB;
BEGIN
    -- 从参数表动态生成CASE分支
    SELECT
        LISTAGG(
            'WHEN ''' || BCM_COLUMN_NAME || ''' THEN tbd.' || BCM_COLUMN_NAME,
            ' '
        ) WITHIN GROUP (ORDER BY BCM_SORT_ORDER)
    INTO v_case_logic
    FROM Ttls_booking_column_mapping
    WHERE BCM_CASE_CATEGORY_CODE = v_target_category;

    -- 构建完整查询语句
    v_full_sql := '
        SELECT
            trd.REQ_ID,
            trd.REQ_CASE_CATEGORY_CODE,
            LISTAGG(
                tbc.BCM_DISPLAY_NAME || '': '' || 
                CASE tbc.BCM_COLUMN_NAME ' || v_case_logic || ' END,
                ''; ''
            ) WITHIN GROUP (ORDER BY tbc.BCM_SORT_ORDER) AS FORMATTED_BOOKING_DETAILS
        FROM
            Tls_Request_Details trd
        INNER JOIN
            tls_booking_details tbd ON trd.REQ_ID = tbd.BOOKING_REQ_ID
        INNER JOIN
            Ttls_booking_column_mapping tbc ON trd.REQ_CASE_CATEGORY_CODE = tbc.BCM_CASE_CATEGORY_CODE
        WHERE trd.REQ_CASE_CATEGORY_CODE = ''' || v_target_category || '''
        GROUP BY
            trd.REQ_ID, trd.REQ_CASE_CATEGORY_CODE';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_full_sql;
END;
/

关键说明：

• 动态从参数表读取字段映射规则，自动生成CASE语句的分支逻辑，无需手动维护SQL中的字段匹配关系。
• 可通过修改v_target_category变量指定要查询的分类编码，灵活适配不同业务场景。

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