一、使用decode函數(shù)進(jìn)行“行轉(zhuǎn)列”：
首先我們來看看最典型的需求：學(xué)生成績單報(bào)表制作
【1】表結(jié)構(gòu)定義：
create table SCORE
(
ID NUMBER(2),
STUDENT_NAME VARCHAR2(10),
SUBJECT VARCHAR2(10),
SCORE NUMBER(6,2)
)
【2】測(cè)試數(shù)據(jù)如下：
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】最終轉(zhuǎn)換的結(jié)果如下：
ID STUDENT_NAME 語法 數(shù)學(xué) 英語
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
【4】實(shí)現(xiàn)方式：
SQL> select id,
2 student_name,
3 sum(decode(subject, 'Chinese', score)) 語法,
4 sum(decode(subject, 'Math', score)) 數(shù)學(xué),
5 sum(decode(subject, 'English', score)) 英語
6 from score
7 group by id, student_name;
ID STUDENT_NAME 語法 數(shù)學(xué) 英語
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
SQL>
這里的原理就是利用decode函數(shù)對(duì)列subject進(jìn)行判斷，如果等于'Chinese'，則將對(duì)應(yīng)的score列的值累加。同理可知其它兩個(gè)字段。
【5】備注：
這種方式有幾個(gè)特點(diǎn)：
①用于判斷的字段其所有可能的值必須是已知的，假如是未知數(shù)則這里的SQL將不成立。
②用于轉(zhuǎn)換的字段通常為數(shù)值型的，因?yàn)橄裥辛修D(zhuǎn)換的情況通常都會(huì)用到累積求和
③用于group by的字段都是主鍵字段，而非用于判斷的字段
【6】延伸需求：
假設(shè)現(xiàn)在我們不是要生成成績單報(bào)表了，我們要統(tǒng)計(jì)一下每個(gè)科目參考的人數(shù)有那幾個(gè)人，其最終的結(jié)果形式如下：
SUBJECT STUDENT
------- ---------
Chinese paul, bob
English paul, bob
Math paul, bob
像這種情況，我們就需要重新定義一下Oracle的'sum'函數(shù)了，這里就涉及到另外一個(gè)專題(以后會(huì)再介紹)
二、使用insert...select進(jìn)行“列轉(zhuǎn)行”：
【1】表結(jié)構(gòu)定義：
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】測(cè)試數(shù)據(jù)如下：
SQL> select * from score_2;
ID STUDENT_NAME CHINESE MATH ENGLISH
---------- --------------- ---------- ---------- ----------
1 paul 70 80 90
2 bob 80 90 100
【3】最終轉(zhuǎn)換的結(jié)果如下：
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】實(shí)現(xiàn)方式：
SQL> set timing on;
SQL>
SQL>
SQL> select id, student_name, '語文' subject, chinese score from score_2 union
2 select id, student_name, '數(shù)學(xué)' 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 數(shù)學(xué) 80
1 paul 英語 90
1 paul 語文 70
2 bob 數(shù)學(xué) 90
2 bob 英語 100
2 bob 語文 80
已選擇6行。
已用時(shí)間: 00: 00: 00.00
可以看到我們利用了SQL里面的union，先提取出所有語文科目的記錄，再連接上所有的數(shù)學(xué)、英語科目的記錄，最后按ID排序就可以了。下面我們來看另外一種實(shí)現(xiàn)方式，我們使用前面第一節(jié)創(chuàng)建的score表作為這次的插入目標(biāo)表：
SQL> set timing on;
SQL> truncate table score;
表已截掉。
已用時(shí)間: 00: 00: 00.01
SQL>
SQL> insert all
2 into score values(id, student_name, '語文', chinese)
3 into score values(id, student_name, '數(shù)學(xué)', math)
4 into score values(id, student_name, '英語', english)
5 select id, student_name, chinese, math, english from score_2;
已創(chuàng)建6行。
已用時(shí)間: 00: 00: 00.00
SQL>
SQL> commit;
提交完成。
已用時(shí)間: 00: 00: 00.00
SQL>
SQL> select * from score;
ID STUDENT_NAME SUBJECT SCORE
---------- --------------- ---------- ----------
1 paul 語文 70
2 bob 語文 80
1 paul 數(shù)學(xué) 80
2 bob 數(shù)學(xué) 90
1 paul 英語 90
2 bob 英語 100
已選擇6行。
已用時(shí)間: 00: 00: 00.00
insert all into ... select是oracle9i里面提供的一個(gè)新功能，用于一次性批量執(zhí)行多個(gè)插入語句，以提高效率。綠色高亮的第二處select語句首先查詢出各個(gè)科目的成績，然后在insert all into語句中引用到了這些查詢到的字段的值。
該SQL語句每次從score_2表中取出一條記錄，然后分別取出其chinese, math, english字段創(chuàng)建一條記錄。依此類推。
三、利用rownum關(guān)鍵字進(jìn)行行列轉(zhuǎn)換：
關(guān)于如何使用rownum進(jìn)行行列轉(zhuǎn)換，請(qǐng)參考本人之前在CSDN的Blog中所寫的一篇文章：利用Oracle rownum完成行列轉(zhuǎn)換一例
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2008-06-15 22:17
Paul Lin 閱讀(6155)
評(píng)論(2) 編輯 收藏 所屬分類:
Oracle 開發(fā)