Oracle SQL中如何拼接Name与Margin_Account生成带点的Member_Account？

在Oracle SQL中，你可以使用**字符串拼接运算符||**实现Name与Margin_Account列的拼接，中间加入英文点号，生成名为Member_Account的新列，写法如下：

NAME || '.' || MARGIN_ACCOUNT AS Member_Account

如果需要处理列值为NULL的情况（避免拼接结果直接变为NULL），可以结合NVL函数将NULL替换为空字符串：

NVL(NAME, '') || '.' || NVL(MARGIN_ACCOUNT, '') AS Member_Account

以下是修改后的完整SQL代码，已添加目标列并修正了原代码中日期格式的错误（将dd-mon-yyy改为dd-mon-yyyy）：

SELECT to_char(intraday_run_time, 'HH24:Mi') as runtime,
       to_char(intraday_run_time, 'dd-mon-yyyy') as business_date,
       REPORT_AMOUNT,
       DESCRIPTION,
       MARGIN_ACCOUNT,
       NAME,
       -- 新增拼接列
       NAME || '.' || MARGIN_ACCOUNT AS Member_Account,
       LEGAL_NAME,
       case
       when to_char(intraday_run_time, 'HH24') < 9 then 'EARLY'
       when to_char(intraday_run_time, 'HH24') < 18 then 'MIDDLE'
       when to_char(intraday_run_time, 'HH24') >= 18 then 'LATE'
       else 'ERROR'
       end as time_of_day,
       case
       when to_char(intraday_run_time, 'dd-mon-yyyy') > add_months(sysdate,-3) then '3month'
       else 'ERROR'
       end as lookback3,
       case when to_char(intraday_run_time, 'dd-mon-yyyy') > add_months(sysdate,-6)
       then '6month'
       else 'ERROR'
       end as lookback6,
       case
       when to_char(intraday_run_time, 'dd-mon-yyyy') > add_months(sysdate,-12)
       then '12month'
       else 'ERROR' end as lookback12
FROM   MEMBER_MANAGER.itd_margin_call_run_log_detail itd
       JOIN (
         SELECT DISTINCT
                MARGIN_CALL_RUN_DETAIL_ID,
                DESCRIPTION,
                margin_account,
                name,
                legal_name
         FROM   member_manager.margin_call_run_log_detail a 
                inner join member_manager.organization b
                on a.organization_ID=b.id
       ) des
       ON des.MARGIN_CALL_RUN_DETAIL_ID = itd.MARGIN_CALL_RUN_DETAIL_ID
WHERE  REPORT_AMOUNT IS NOT NULL
AND    DESCRIPTION in( 'Intraday Margin Call from ECS Call Amount Combined', 'Manually Added' )
AND    TRUNC(intraday_run_time) between add_months(sysdate,-12) and sysdate
order by 1 desc

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