【SQL】使用insert all + row_number()语法将数据灌装到两个表中

这个是 我 为论坛上一位朋友解决问题的一个回帖，感觉也是挺有意义的一个解决方案，记录一下。

论坛帖子地址： http://www.itpub.net/forum.php?mod=viewthread&tid=1503973&page=1#pid18449834

问题描述：
-------------------------------------------

有三个表 

test1（col1,col2,col3,col4,col5,col6） 数据源表 （col1,col2,col3 用来分类）

test2（col1,col2,col3）                           用于存放 类别 的目标表

test3（col4,col5,col6）                           用于存放 明细 的目标表

需求是，将test1中的数据按照col1,col2,col3来分类，将col1,col2,col3相同的类别数据放入test2表中，将明细数据放入test3中。

其实简单的做法的话是，将test1两次查询，分别将结果放入test2和test3中：

第一次     insert into test2 select disctinct col1,col2,col3 from test1;

第二次     insert into test3 select col4,col5,col6 from test1;

但是考虑到test1可能会很大，两次查询可能会带来一些性能问题，所以我提供了一种利用insert all + row_number()语法，只检索一次test1表，将结果分别灌入test2，test3中。
---------------------------------------

我的回帖：
--------------------------------------------------------
需求大体了解，使用insert all + row_number()语法 应该可以实现。

简单模拟了一下，你看看是不是你想要的效果。

-- 创建测试表
SQL> create table test1 (col1 varchar(5),col2 varchar(5),col3 varchar(5),col4 varchar(5),col5 varchar(5),col6 varchar(5));

Table created

SQL> create table test2 (col1 varchar(5),col2 varchar(5),col3 varchar(5));

Table created

SQL> create table test3 (col4 varchar(5),col5 varchar(5),col6 varchar(5));

Table created

-- 向测试表test1表中插入测试数据
SQL> insert into test1 values('A0001','B0001','C0001','D0001','E0001','F0001');

1 row inserted

SQL> insert into test1 values('A0001','B0001','C0001','D0002','E0002','F0002');

1 row inserted

SQL> insert into test1 values('A0001','B0001','C0001','D0003','E0003','F0003');

1 row inserted

SQL> insert into test1 values('A0004','B0004','C0004','D0004','E0004','F0004');

1 row inserted

SQL> insert into test1 values('A0005','B0005','C0005','D0005','E0005','F0005');

1 row inserted

SQL> commit;

Commit complete

-- 使用insert all 及row_number()语法实现有选择地向test2和test3中插入数据
SQL> 
SQL> insert all
  2  when  (rcnt = 1) then
  3      into test2  (col1, col2, col3) values  (col1, col2, col3)
  4  when  (rcnt is not null) then
  5      into test3  (col4, col5, col6) values  (col4, col5, col6)
  6  select col1,
  7           col2,
  8           col3,
  9           row_number() over(partition by col1, col2, col3 order by col1, col2, col3) rcnt,
10           col4,
11           col5,
12           col6
13  from test1;

8 rows inserted

SQL> commit;

Commit complete

SQL> select * from test1;

COL1  COL2  COL3  COL4  COL5  COL6
----- ----- ----- ----- ----- -----
A0001 B0001 C0001 D0001 E0001 F0001
A0001 B0001 C0001 D0002 E0002 F0002
A0001 B0001 C0001 D0003 E0003 F0003
A0004 B0004 C0004 D0004 E0004 F0004
A0005 B0005 C0005 D0005 E0005 F0005

SQL> select * from test2;

COL1  COL2  COL3
----- ----- -----
A0001 B0001 C0001
A0004 B0004 C0004
A0005 B0005 C0005

SQL> select * from test3;

COL4  COL5  COL6
----- ----- -----
D0001 E0001 F0001
D0002 E0002 F0002
D0003 E0003 F0003
D0004 E0004 F0004
D0005 E0005 F0005

SQL>
-------------------------------------------

以上。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20335819/viewspace-709674/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20335819/viewspace-709674/