Oracle 基于条件连接的合并-展开记录

嗨，团队，

在更改合并语句时需要您的帮助或建议。

我有一个下面的暂存表A_TRANSACTION_STAGING，它被合并到主表A_TRANSACTION:

交易阶段:

TRANSACTION_ID  NUMBER
REGION_CD       VARCHAR2(5)
CHANNEL         VARCHAR2(15)
GROUP           VARCHAR2(15)
MATERIAL        VARCHAR2(20)
PRICE           DECIMAL(10,2)
COUNTRY         VARCHAR2(15)
复制

A_TRANSACTION:

TRANSACTION_ID  NUMBER
REGION_CD       VARCHAR2(5)
CHANNEL         VARCHAR2(15)
GROUP           VARCHAR2(15)
MATERIAL        VARCHAR2(20)
PRICE           DECIMAL(10,2)
CREATED_DATE    DATE
MODIFIED_DATE   DATE
复制

合并查询:

MERGE INTO A_TRANSACTION tgt
USING (SELECT TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL, PRICE, SYSDATE AS CREATED_DATE, SYSDATE AS MODIFIED_DATE
              FROM  (
                     SELECT TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL, PRICE, ROW_NUMBER() over (partition by TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL order by rowid desc ) last_rec FROM A_TRANSACTION_STAGING) WHERE last_rec = 1
       ) src
ON (tgt.TRANSACTION_ID = src.TRANSACTION_ID AND tgt.REGION_CD = src.REGION_CD AND tgt.CHANNEL = src.CHANNEL AND tgt.GROUP = src.GROUP AND tgt.MATERIAL = src.MATERIAL)
WHEN matched THEN
  UPDATE SET tgt.PRICE = src.PRICE,
             tgt.modified_date = src.modified_date
WHEN NOT matched THEN
  INSERT (tgt.TRANSACTION_ID,
          tgt.REGION_CD,
          tgt.CHANNEL,
          tgt.GROUP,
          tgt.MATERIAL,
          tgt.PRICE,
          tgt.CREATED_DATE,
          tgt.MODIFIED_DATE)
  VALUES (src.TRANSACTION_ID,
          src.REGION_CD,
          src.CHANNEL,
          src.GROUP,
          src.MATERIAL,
          src.PRICE,
          src.CREATED_DATE,
          src.MODIFIED_DATE);
复制

现在的要求是基于表B_RULE的条件连接来展开暂存表中的记录

B_ 规则:

REGION_CD    VARCHAR2(5)
CHANNEL      VARCHAR2(15)
GROUP        VARCHAR2(15)
复制

例如:

如果A_TRANSACTION_STAGING记录为:

TRANSACTION_ID|REGION_CD|CHANNEL|GROUP|MATERIAL|PRICE|COUNTRY|
158963|AMR|CUSTOMER|ONLINE|PMGHJTE|125|USA|
158964|EUR| | |OTGHYW|120|IRL|
复制

如果B_RULE记录是:

REGION_CD|COUNTRY|CHANNEL|GROUP|
EUR|IRL|CUSTOMER|ONLINE|
EUR|IRL|EDUCATION|RESELLER|
复制

由于A_TRANSACTION_STAGING中的REGION_CD值为 'EUR'，而COUNTRY为 'IRL'，它存在于B_RULE表中，因此我需要在更新A_TRANSACTION时使用B_RULE中的所有通道和组值。如果B_RULE中不存在，它应该按现状合并。

以上记录应合并到A_TRANSACTION中，如下所示:

TRANSACTION_ID|REGION_CD|CHANNEL|GROUP|MATERIAL|PRICE|CREATED_DATE|MODIFIED_DATE
158963|AMR|CUSTOMER|ONLINE|PMGHJTE|125|01-NOV-2017|01-NOV-2017
158964|EUR|CUSTOMER|ONLINE|OTGHYW|120|01-OCT-2017|01-NOV-2017
158964|EUR|EDUCATION|RESELLER|OTGHYW|120|01-OCT-2017|01-NOV-2017
复制

此外，我还需要跳过B_RULE匹配记录的插入。它应该只更新可用的记录。
这是可以在单个合并中处理的，还是应该在自定义过程中处理。

谢谢。

所以在我看来，你只需要外部join b_rule到合并的using子句中的工作表:

create table A_TRANSACTION_STAGING (
TRANSACTION_ID  NUMBER,
REGION_CD       VARCHAR2(5),
CHANNEL         VARCHAR2(15),
GRP             VARCHAR2(15),
MATERIAL        VARCHAR2(20),
PRICE           DECIMAL(10,2),
COUNTRY         VARCHAR2(15)
);

create table A_TRANSACTION (
TRANSACTION_ID  NUMBER,
REGION_CD       VARCHAR2(5),
CHANNEL         VARCHAR2(15),
GRP             VARCHAR2(15),
MATERIAL        VARCHAR2(20),
PRICE           DECIMAL(10,2),
CREATED_DATE    DATE,
MODIFIED_DATE   DATE
);

create table B_RULE (
REGION_CD    VARCHAR2(5),
COUNTRY      VARCHAR2(3),
CHANNEL      VARCHAR2(15),
GRP          VARCHAR2(15)
);

insert into A_TRANSACTION_STAGING values (
158963,'AMR','CUSTOMER','ONLINE','PMGHJTE','125','USA'
);
insert into A_TRANSACTION_STAGING values (
158964,'EUR',null,null,'OTGHYW','120','IRL'
);

insert into b_rule values ('EUR','IRL','CUSTOMER','ONLINE');
insert into b_rule values ('EUR','IRL','EDUCATION','RESELLER');


MERGE INTO A_TRANSACTION tgt
USING (select a.transaction_id, 
       a.material,
       a.price,
       coalesce(b.country, a.country) country,
       coalesce(b.region_cd, a.region_cd) region_cd,
       coalesce(b.channel, a.channel) channel,
       coalesce(b.grp, a.grp) grp,
       sysdate as created_date, sysdate as modified_date
from   a_transaction_staging a
left join b_rule b
on    a.country = b.country
and   b.region_cd = b.region_cd
      ) src
ON (tgt.TRANSACTION_ID = src.TRANSACTION_ID AND tgt.REGION_CD = src.REGION_CD AND tgt.CHANNEL = src.CHANNEL AND tgt.GRP = src.GRP AND tgt.MATERIAL = src.MATERIAL)
WHEN matched THEN
  UPDATE SET tgt.PRICE = src.PRICE,
             tgt.modified_date = src.modified_date
WHEN NOT matched THEN
  INSERT (tgt.TRANSACTION_ID,
          tgt.REGION_CD,
          tgt.CHANNEL,
          tgt.GRP,
          tgt.MATERIAL,
          tgt.PRICE,
          tgt.CREATED_DATE,
          tgt.MODIFIED_DATE)
  VALUES (src.TRANSACTION_ID,
          src.REGION_CD,
          src.CHANNEL,
          src.GRP,
          src.MATERIAL,
          src.PRICE,
          src.CREATED_DATE,
          src.MODIFIED_DATE);
          
select * from A_TRANSACTION;

TRANSACTION_ID   REGION_CD   CHANNEL     GRP        MATERIAL   PRICE   CREATED_DATE           MODIFIED_DATE          
          158963 AMR         CUSTOMER    ONLINE     PMGHJTE        125 02-NOV-2017 04:08:39   02-NOV-2017 04:08:39   
          158964 EUR         CUSTOMER    ONLINE     OTGHYW         120 02-NOV-2017 04:08:39   02-NOV-2017 04:08:39   
          158964 EUR         EDUCATION   RESELLER   OTGHYW         120 02-NOV-2017 04:08:39   02-NOV-2017 04:08:39 
复制

我不确定你的意思是:

Further, i also need to skip insert for B_RULE matched records. It should only update the available records.

但是您可以将where子句添加到when (不) 匹配子句中。因此，您可以根据需要使用它来停止插入或更新行。

PS-请以 “创建表” 和 “插入” 的形式提供您的表和数据。这不仅意味着我们可以更快地为您提供答案，而且构建测试用例的可能性也较小。