本的sql 语句 和例子转自:
http://space.itpub.net/25724608/viewspace-695818
oracle 有一种insert 语句叫 pivoting insert.
直译就是旋转插入了.
下面会用个例子做个简单说明.
首先建表两张表 sales_source_data, sales_info
建表sql:
sales_source_data:
create table sales_source_data (
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thur number(8,2),
sales_fri number(8,2)
);
sales_info:
create table sales_info (
employee_id number(6),
week number(2),
sales number(8,2)
);
接着对表 sales_source_data 插入一行数据.
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
好了, 现在的需求是把表 sales_source_data 的数据
EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI
----------- ---------- ---------- ---------- ---------- ---------- ----------
176 6 2000 3000 4000 5000 6000
导入到 另一张表 sales_info中.
很明显, 由于sales_source_data 是具有7个字段的. 而sales_info只有3个字段.
普通同的insert 方法不能胜任.
而有一种Insert 语句能将 原本横向的数据
EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI
变成竖向的
EMPLOYEE_ID
WEEK_ID
SALES_MON
EMPLOYEE_ID
WEEK_ID
SALES_TUE
EMPLOYEE_ID
WEEK_ID
SALES_WED
EMPLOYEE_ID
WEEK_ID
SALES_THUR
EMPLOYEE_ID
WEEK_ID
SALES_FRI
5条数据 插入到sale_info 表.
看起来就想把打横的数据旋转了45度 变成打竖的数据(列转行)
所以这种insert 语句叫做 旋转insert (pivoting insert)
sql语句如下:
insert all
into sales_info values(employee_id, week_id, sales_mon)
into sales_info values(employee_id, week_id, sales_tue)
into sales_info values(employee_id, week_id, sales_wed)
into sales_info values(employee_id, week_id, sales_thur)
into sales_info values(employee_id, week_id, sales_fri)
select employee_id, week_id, sales_mon, sales_tue,
sales_wed, sales_thur,sales_fri
from sales_source_data;
见到 Insert all 这个关键字了吧, 没错, 可见pivoting insert 也是 mutitable insert 的一种.
关于 mutitable insert 有如下重点:
INSERT [ALL | FIRST]
例子:
Insert All
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from z_test;
关键就是Insert 后面 All 和 insert 的区别
当使用FIRST关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,