CONAN ZONE

你越挣扎我就越兴奋

BlogJava 首页 新随笔 联系 聚合 管理
  0 Posts :: 282 Stories :: 0 Comments :: 0 Trackbacks
一、使用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(2primary 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行。

已用时间:  
000000.00

可以看到我们利用了SQL里面的union,先提取出所有语文科目的记录,再连接上所有的数学、英语科目的记录,最后按ID排序就可以了。下面我们来看另外一种实现方式,我们使用前面第一节创建的score表作为这次的插入目标表:
SQL> set timing on;
SQL
> truncate table score;

表已截掉。

已用时间:  
000000.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行。

已用时间:  
000000.00
SQL
> 
SQL
> commit;

提交完成。

已用时间:  
000000.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行。

已用时间:  
000000.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 阅读(185) 评论(0)  编辑  收藏 所属分类: Oracle