Oracle 19C中从Staging表向主表条件插入数据的需求

以下针对你提出的三个场景，给出基于Oracle 19C的SQL实现方案：

场景1：优先保留-b结尾的S记录，忽略-n的S，I/N记录全插入 #

需求回顾 #

若Staging表存在pool_name以-b结尾且flag='S'的记录，仅插入该类S记录，忽略-n结尾的S记录；flag='I'和flag='N'的记录始终插入。

实现SQL #

INSERT INTO main_table (pool_name, account, flag)
SELECT pool_name, account, flag
FROM staging_table st
WHERE 
  -- 直接保留I、N类型的所有记录
  flag IN ('I', 'N')
  OR
  -- 处理S类型记录：优先保留-b结尾的，无-b的S时才保留其他S
  (
    flag = 'S'
    AND (
      pool_name LIKE '%-b'
      OR NOT EXISTS (
        SELECT 1 
        FROM staging_table 
        WHERE flag = 'S' AND pool_name LIKE '%-b'
      )
    )
  );

逻辑说明 #

• 第一部分直接筛选所有I、N记录，确保全部插入
• 第二部分针对S记录：
  • 若存在任何-b结尾的S记录，仅保留这类记录
  • 若不存在-b结尾的S记录，则保留所有S记录（包括-n的）
• 对应示例数据，最终会插入pool-b/acc1/S、pool-b/acc1/I、pool-n/acc2/N三条记录，符合需求

场景2：无-b的S时才插入-n的S，主表已有-b的S则不插入-n的S #

需求回顾 #

当Staging表不存在pool_name以-b结尾且flag='S'的记录时，插入-n结尾的S记录；若主表已存在-b结尾的S记录，则不插入-n的S记录。

实现SQL #

INSERT INTO main_table (pool_name, account, flag)
SELECT pool_name, account, flag
FROM staging_table st
WHERE 
  -- 直接保留I、N类型的所有记录
  flag IN ('I', 'N')
  OR
  -- 仅当Staging无-b的S，且主表也无-b的S时，才插入-n的S
  (
    flag = 'S'
    AND pool_name LIKE '%-n'
    AND NOT EXISTS (
      SELECT 1 
      FROM staging_table 
      WHERE flag = 'S' AND pool_name LIKE '%-b'
    )
    AND NOT EXISTS (
      SELECT 1 
      FROM main_table 
      WHERE flag = 'S' AND pool_name LIKE '%-b'
    )
  );

逻辑说明 #

• I、N记录直接插入，逻辑同场景1
• 针对-n的S记录，需同时满足两个条件：
  1. Staging表中没有任何-b结尾的S记录
  2. 主表中也没有任何-b结尾的S记录
• 对应示例数据，Staging无-b的S，主表也无该类记录，因此会插入pool-n/acc2/S，加上I、N记录共三条

场景3：无-n的S时插入-b的S，主表有-n的S则用-b的S覆盖 #

需求回顾 #

当Staging表不存在pool_name以-n结尾且flag='S'的记录时，插入-b结尾的S记录；若主表已存在-n结尾的S记录，则用Staging的-b的S记录覆盖主表对应记录。

实现SQL #

-- 第一步：插入I、N类型记录（若主表有重复限制，需加NOT EXISTS避免重复）
INSERT INTO main_table (pool_name, account, flag)
SELECT pool_name, account, flag
FROM staging_table
WHERE flag IN ('I', 'N')
AND NOT EXISTS (
  SELECT 1 
  FROM main_table mt 
  WHERE mt.pool_name = staging_table.pool_name 
    AND mt.account = staging_table.account 
    AND mt.flag = staging_table.flag
);

-- 第二步：处理S记录的插入与覆盖（使用MERGE实现）
MERGE INTO main_table mt
USING (
  SELECT pool_name, account, flag
  FROM staging_table
  WHERE flag = 'S'
) st
ON (
  -- 匹配条件：主表存在-n结尾的S记录，且Staging有-b结尾的S记录
  mt.pool_name LIKE '%-n' 
  AND mt.flag = 'S' 
  AND st.pool_name LIKE '%-b'
)
WHEN MATCHED THEN
  -- 覆盖主表的-n的S记录为Staging的-b的S记录
  UPDATE SET 
    mt.pool_name = st.pool_name, 
    mt.account = st.account, 
    mt.flag = st.flag
WHEN NOT MATCHED THEN
  -- 仅当Staging无-n的S记录时，插入-b的S记录
  INSERT (pool_name, account, flag)
  VALUES (st.pool_name, st.account, st.flag)
WHERE NOT EXISTS (
  SELECT 1 
  FROM staging_table 
  WHERE flag = 'S' AND pool_name LIKE '%-n'
);

逻辑说明 #

1. 先处理I、N记录：直接插入，通过NOT EXISTS避免主表已存在的重复记录
2. 用MERGE语句处理S记录的复杂逻辑：
   • 匹配时更新：当主表存在-n的S记录，且Staging有-b的S记录时，将主表的该条记录替换为Staging的-b的S记录
   • 不匹配时插入：仅当Staging表中没有任何-n的S记录时，才插入Staging的-b的S记录

• 对应示例数据，Staging无-n的S，因此会插入pool-b/acc2/S，加上I、N记录共三条

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