CONAN ZONE

你越掙扎我就越興奮

BlogJava

管理

0 Posts :: 282 Stories :: 0 Comments :: 0 Trackbacks

留言簿(6)

文章分類(325)

文章檔案(282)

guy's blog

square's blog

搜索

積分與排名

積分 - 410104
排名 - 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> 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 閱讀(195) 評論(0) 編輯收藏所屬分類: Oracle