一、使用decode函数进行“行转列”:
首先我们来看看最典型的需求:学生成绩单报表制作
【1】表结构定义:
【2】测试数据如下:
【3】最终转换的结果如下:
【4】实现方式:
这里的原理就是利用decode函数对列subject进行判断,如果等于'Chinese',则将对应的score列的值累加。同理可知其它两个字段。
【5】备注:
这种方式有几个特点:
①用于判断的字段其所有可能的值必须是已知的,假如是未知数则这里的SQL将不成立。
②用于转换的字段通常为数值型的,因为像行列转换的情况通常都会用到累积求和
③用于group by的字段都是主键字段,而非用于判断的字段
【6】延伸需求:
假设现在我们不是要生成成绩单报表了,我们要统计一下每个科目参考的人数有那几个人,其最终的结果形式如下:
像这种情况,我们就需要重新定义一下Oracle的'sum'函数了,这里就涉及到另外一个专题(以后会再介绍)
二、使用insert...select进行“列转行”:
【1】表结构定义:
【2】测试数据如下:
【3】最终转换的结果如下:
也就是说和第一种情况刚好是相反的。
【4】实现方式:
可以看到我们利用了SQL里面的union,先提取出所有语文科目的记录,再连接上所有的数学、英语科目的记录,最后按ID排序就可以了。下面我们来看另外一种实现方式,我们使用前面第一节创建的score表作为这次的插入目标表:
insert all into ... select是oracle9i里面提供的一个新功能,用于一次性批量执行多个插入语句,以提高效率。绿色高亮的第二处select语句首先查询出各个科目的成绩,然后在insert all into语句中引用到了这些查询到的字段的值。
该SQL语句每次从score_2表中取出一条记录,然后分别取出其chinese, math, english字段创建一条记录。依此类推。
三、利用rownum关键字进行行列转换:
关于如何使用rownum进行行列转换,请参考本人之前在CSDN的Blog中所写的一篇文章:利用Oracle rownum完成行列转换一例
-------------------------------------------------------------
生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。
首先我们来看看最典型的需求:学生成绩单报表制作
【1】表结构定义:
create
table
SCORE
(
ID NUMBER ( 2 ),
STUDENT_NAME VARCHAR2 ( 10 ),
SUBJECT VARCHAR2 ( 10 ),
SCORE NUMBER ( 6 , 2 )
)
(
ID NUMBER ( 2 ),
STUDENT_NAME VARCHAR2 ( 10 ),
SUBJECT VARCHAR2 ( 10 ),
SCORE NUMBER ( 6 , 2 )
)
【2】测试数据如下:
SQL
>
select
*
from
score;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已选择6行。
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已选择6行。
【3】最终转换的结果如下:
ID STUDENT_NAME 语法 数学 英语
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
【4】实现方式:
SQL
>
select
id,
2 student_name,
3 sum (decode(subject, ' Chinese ' , score)) 语法,
4 sum (decode(subject, ' Math ' , score)) 数学,
5 sum (decode(subject, ' English ' , score)) 英语
6 from score
7 group by id, student_name;
ID STUDENT_NAME 语法 数学 英语
-- -------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
SQL >
2 student_name,
3 sum (decode(subject, ' Chinese ' , score)) 语法,
4 sum (decode(subject, ' Math ' , score)) 数学,
5 sum (decode(subject, ' English ' , score)) 英语
6 from score
7 group by id, student_name;
ID STUDENT_NAME 语法 数学 英语
-- -------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
SQL >
这里的原理就是利用decode函数对列subject进行判断,如果等于'Chinese',则将对应的score列的值累加。同理可知其它两个字段。
【5】备注:
这种方式有几个特点:
①用于判断的字段其所有可能的值必须是已知的,假如是未知数则这里的SQL将不成立。
②用于转换的字段通常为数值型的,因为像行列转换的情况通常都会用到累积求和
③用于group by的字段都是主键字段,而非用于判断的字段
【6】延伸需求:
假设现在我们不是要生成成绩单报表了,我们要统计一下每个科目参考的人数有那几个人,其最终的结果形式如下:
SUBJECT STUDENT
------- ---------
Chinese paul, bob
English paul, bob
Math paul, bob
------- ---------
Chinese paul, bob
English paul, bob
Math paul, bob
像这种情况,我们就需要重新定义一下Oracle的'sum'函数了,这里就涉及到另外一个专题(以后会再介绍)
二、使用insert...select进行“列转行”:
【1】表结构定义:
create
table
score_2
(
id number ( 2 ) primary key ,
student_name varchar2 ( 10 ),
chinese number ( 6 , 2 ),
math number ( 6 , 2 ),
english number ( 6 , 2 )
)
(
id number ( 2 ) primary key ,
student_name varchar2 ( 10 ),
chinese number ( 6 , 2 ),
math number ( 6 , 2 ),
english number ( 6 , 2 )
)
【2】测试数据如下:
SQL
>
select
*
from
score_2;
ID STUDENT_NAME CHINESE MATH ENGLISH
-- -------- --------------- ---------- ---------- ----------
1 paul 70 80 90
2 bob 80 90 100
ID STUDENT_NAME CHINESE MATH ENGLISH
-- -------- --------------- ---------- ---------- ----------
1 paul 70 80 90
2 bob 80 90 100
【3】最终转换的结果如下:
SQL
>
select
*
from
score;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已选择6行。
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已选择6行。
也就是说和第一种情况刚好是相反的。
【4】实现方式:
SQL
>
set
timing
on
;
SQL >
SQL >
SQL > select id, student_name, ' 语文 ' subject, chinese score from score_2 union
2 select id, student_name, ' 数学 ' subject, math score from score_2 union
3 select id, student_name, ' 英语 ' subject, english score from score_2
4 order by id;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul 数学 80
1 paul 英语 90
1 paul 语文 70
2 bob 数学 90
2 bob 英语 100
2 bob 语文 80
已选择6行。
已用时间: 00 : 00 : 00.00
SQL >
SQL >
SQL > select id, student_name, ' 语文 ' subject, chinese score from score_2 union
2 select id, student_name, ' 数学 ' subject, math score from score_2 union
3 select id, student_name, ' 英语 ' subject, english score from score_2
4 order by id;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul 数学 80
1 paul 英语 90
1 paul 语文 70
2 bob 数学 90
2 bob 英语 100
2 bob 语文 80
已选择6行。
已用时间: 00 : 00 : 00.00
可以看到我们利用了SQL里面的union,先提取出所有语文科目的记录,再连接上所有的数学、英语科目的记录,最后按ID排序就可以了。下面我们来看另外一种实现方式,我们使用前面第一节创建的score表作为这次的插入目标表:
SQL
>
set
timing
on
;
SQL > truncate table score;
表已截掉。
已用时间: 00 : 00 : 00.01
SQL >
SQL > insert all
2 into score values (id, student_name, ' 语文 ' , chinese)
3 into score values (id, student_name, ' 数学 ' , math)
4 into score values (id, student_name, ' 英语 ' , english)
5 select id, student_name, chinese, math, english from score_2;
已创建6行。
已用时间: 00 : 00 : 00.00
SQL >
SQL > commit ;
提交完成。
已用时间: 00 : 00 : 00.00
SQL >
SQL > select * from score;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul 语文 70
2 bob 语文 80
1 paul 数学 80
2 bob 数学 90
1 paul 英语 90
2 bob 英语 100
已选择6行。
已用时间: 00 : 00 : 00.00
SQL > truncate table score;
表已截掉。
已用时间: 00 : 00 : 00.01
SQL >
SQL > insert all
2 into score values (id, student_name, ' 语文 ' , chinese)
3 into score values (id, student_name, ' 数学 ' , math)
4 into score values (id, student_name, ' 英语 ' , english)
5 select id, student_name, chinese, math, english from score_2;
已创建6行。
已用时间: 00 : 00 : 00.00
SQL >
SQL > commit ;
提交完成。
已用时间: 00 : 00 : 00.00
SQL >
SQL > select * from score;
ID STUDENT_NAME SUBJECT SCORE
-- -------- --------------- ---------- ----------
1 paul 语文 70
2 bob 语文 80
1 paul 数学 80
2 bob 数学 90
1 paul 英语 90
2 bob 英语 100
已选择6行。
已用时间: 00 : 00 : 00.00
insert all into ... select是oracle9i里面提供的一个新功能,用于一次性批量执行多个插入语句,以提高效率。绿色高亮的第二处select语句首先查询出各个科目的成绩,然后在insert all into语句中引用到了这些查询到的字段的值。
该SQL语句每次从score_2表中取出一条记录,然后分别取出其chinese, math, english字段创建一条记录。依此类推。
三、利用rownum关键字进行行列转换:
关于如何使用rownum进行行列转换,请参考本人之前在CSDN的Blog中所写的一篇文章:利用Oracle rownum完成行列转换一例
-------------------------------------------------------------
生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。