SQL调节语句 [英] SQL Statement for Reconciliation

问题描述

给出以下架构:

create table TBL1 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100));
create table TBL2 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100));
create table TBL_RESULT (ID varchar2(100) primary key not null, TBL1_ID varchar2(100), TBL2_ID varchar2(100));

create unique index UK_TBL_RESULT_TBL1_ID on TBL_RESULT(TBL1_ID);
create unique index UK_TBL_RESULT_TBL2_ID on TBL_RESULT(TBL2_ID);

create sequence SEQ_TBL_RESULT;

insert into TBL1 VALUES('1', '1');
insert into TBL1 VALUES('2', '1');
insert into TBL1 VALUES('3', '1');

insert into TBL2 VALUES('4', '1');
insert into TBL2 VALUES('5', '1');
insert into TBL2 VALUES('6', '1');

我需要一条SQL语句，该语句将通过调和TBL1和TBL2的相等MATCH_CRITERIA来产生结果.

I need an SQL statement that will produce the result by reconciling equal MATCH_CRITERIA from TBL1 and TBL2.

如果没有 UNIQUE约束，以下操作将起作用.但是，我们在应用程序中需要唯一的约束.

The following would work given that the UNIQUE constraint is not present. However, we need the unique constraints in our application.

insert into TBL_RESULT (ID, TBL1_ID, TBL2_ID)
select SEQ_TBL_RESULT.nextval, TBL1.ID, TBL2.ID
from TBl1, TBL2
where TBL1.MATCH_CRITERIA = TBL2.MATCH_CRITERIA;

TBL_RESULT的示例输出

| ID  |  TBL1_ID | TBL2_ID |
| '1' |  '1'     |    '5'  |
| '2' |  '2'     |    '4'  |
| '3' |  '3'     |    '6'  |

注意:"1"与"4"或"6"匹配并不重要.只要两个表中的MATCH_CRITERIA相等，并且结果表具有唯一的TBL1_ID和TBL2_ID.

Note: it doesn't matter if '1' is matched with '4' or '6'. As long as the MATCH_CRITERIA from both tables are equal and the resulting table has unique TBL1_ID and TBL2_ID.

请注意，我们正在插入来自TBL1的一百万条记录和来自TBL2的另一百万条记录.因此，使用PL/SQL进行顺序插入是不可接受的，除非它可以真正快速地运行(少于15分钟).

Note that we are inserting a million records from TBL1 and another million records from TBL2. Thus, sequential insert using PL/SQL is not acceptable unless it can run really fast (less than 15 minutes).

推荐答案

如何操作:

  INSERT INTO TBL_RESULT (ID, TBL1_ID, TBL2_ID) 
  SELECT seq_tbl_result.nextval,t1.id,t2.id 
  FROM
  (SELECT t1.match_criteria,t1.id, row_number() OVER (PARTITION BY t1.match_criteria ORDER BY t1.id) rn 
   FROM tbl1 t1) t1,  
  (SELECT t2.match_criteria,t2.id, row_number() OVER (PARTITION BY t2.match_criteria ORDER BY t2.id) rn 
   FROM tbl2 t2) t2
  WHERE t1.match_criteria=t2.match_criteria AND t1.rn=t2.rn

注意:假设两个表中每个匹配集中的行数相等.

Note: It assumes that there are an equal number of rows in each matching set in both tables.

这篇关于SQL调节语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！