SQL对账语句

Question

给定以下模式：

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 来产生结果。

如果没有 UNIQUE 约束条件的话，下面的代码可以工作。但是，在我们的应用程序中，我们需要唯一约束条件。

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即可。

请注意，我们正在从TBL1和TBL2中插入100万条记录。因此，除非它可以运行得非常快（少于15分钟），否则不接受使用PL/SQL进行顺序插入。

- John

Answer 1

这个怎么样：

  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

注意：它假设在两个表中每个匹配集中的行数相等。

- Kevin Burton

---

太棒了！我会再多试试看。谢谢！ - John

即使有更多的列需要匹配，也能很好地工作。只需确保这些列在分区中列出即可。 - John

Answer 2

这样怎么样：

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
AND (NOT EXISTS (SELECT 1 FROM TBL_RESULT WHERE TBL1_ID = TBL1.ID) OR NOT EXISTS(SELECT 1 FROM TBL_RESULT WHERE TBL2_ID = TBL2.id))

这应该可以防止违反您的唯一约束。

- Code Magician

---

这样做会不会只留下每个匹配条件的ID的单个值？显然，John想要保留每个表中的ID数量（至少这是我理解示例输出的方式）。 - user330315

好的，谢谢指出。我误解了，但我会修改我的回答。 - Code Magician