CONAN ZONE
你越挣扎我就越兴奋
BlogJava
首页
新随笔
联系
聚合
管理
0 Posts :: 282 Stories :: 0 Comments :: 0 Trackbacks
留言簿
(6)
给我留言
查看公开留言
查看私人留言
文章分类
(325)
Application Server(4)
CVS(4)
Database(7)
Eclipse(7)
ExtJs(1)
GlassFish(1)
Hibernate(3)
HTML/CSS(3)
Italian(1)
J2EE(24)
J2SE(15)
JAVA(27)
JMS(2)
JQuery(7)
JS(12)
JVM(5)
Linux/Unix(21)
Mylyn(2)
NetBeans(4)
NoSql(1)
Oracle(27)
PowerDesigner(1)
Python(1)
Solr(15)
Spring(11)
SQL(13)
Struts(2)
Struts2/Webwork(12)
SVN(7)
VIM(13)
Web Service(13)
web技术(4)
其他技术(13)
养生保健(7)
动漫(1)
外语(2)
新闻娱乐(19)
旅游(1)
模式(1)
模板(2)
生活(2)
项目管理(7)
文章档案
(282)
2012年6月 (3)
2012年5月 (12)
2011年12月 (3)
2011年11月 (2)
2011年7月 (1)
2011年5月 (2)
2011年4月 (4)
2011年3月 (1)
2011年2月 (4)
2010年12月 (2)
2010年11月 (7)
2010年9月 (2)
2010年8月 (1)
2010年7月 (3)
2010年5月 (2)
2010年4月 (3)
2010年3月 (2)
2009年12月 (1)
2009年11月 (1)
2009年10月 (3)
2009年9月 (3)
2009年8月 (6)
2009年7月 (6)
2009年6月 (3)
2009年5月 (2)
2009年4月 (8)
2009年3月 (1)
2009年2月 (15)
2009年1月 (9)
2008年12月 (2)
2008年11月 (11)
2008年10月 (1)
2008年9月 (2)
2008年8月 (3)
2008年7月 (54)
2008年6月 (97)
guy's blog
ccsoft's blog
Ray's blog
square's blog
刘佳's blog
搜索
积分与排名
积分 - 406590
排名 - 136
最新评论
Oracle开发专题之:行列转换
一、使用decode函数进行“行转列”:
首先我们来看看最典型的需求:学生成绩单报表制作
【1】表结构定义:
create
table
SCORE
(
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行。
【3】最终转换的结果如下:
ID STUDENT_NAME 语法 数学 英语
---------- --------------- ---------- ---------- ----------
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
>
这里的原理就是利用decode函数对列subject进行判断,如果等于'Chinese',则将对应的score列的值累加。同理可知其它两个字段。
【5】备注:
这种方式有几个特点:
①用于判断的字段其所有可能的值必须是已知的,假如是未知数则这里的SQL将不成立。
②用于转换的字段通常为数值型的,因为像行列转换的情况通常都会用到累积求和
③用于group by的字段都是主键字段,而非用于判断的字段
【6】延伸需求:
假设现在我们不是要生成成绩单报表了,我们要统计一下每个科目参考的人数有那几个人,其最终的结果形式如下:
SUBJECT STUDENT
------- ---------
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
)
)
【2】测试数据如下:
SQL
>
select
*
from
score_2;
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行。
也就是说和第一种情况刚好是相反的。
【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里面的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
insert all into ... select是oracle9i里面提供的一个新功能,用于一次性批量执行多个插入语句,以提高效率。绿色高亮的第二处select语句首先查询出各个科目的成绩,然后在insert all into语句中引用到了这些查询到的字段的值。
该SQL语句每次从score_2表中取出一条记录,然后分别取出其chinese, math, english字段创建一条记录。依此类推。
三、利用rownum关键字进行行列转换:
关于如何使用rownum进行行列转换,请参考本人之前在CSDN的Blog中所写的一篇文章:
利用Oracle rownum完成行列转换一例
posted on 2008-06-22 18:53
CONAN
阅读(186)
评论(0)
编辑
收藏
所属分类:
Oracle
Powered by:
BlogJava
Copyright © CONAN