點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

oracle行列互轉常用小技巧總結

來源：懂視網責編：小采時間：2020-11-09 15:38:20

oracle行列互轉常用小技巧總結

oracle行列互轉常用小技巧總結:平時做報表統計數據時，常遇到行列互轉，最后有總計等要求的報表式。注：下面的sql都是在oracle 10g下寫的。假如現有一張這樣的數據表： SELECT * FROM studentscores ; 多行轉多列現客戶要求這樣的式顯示這樣的式來顯示成績。方法一： WITH t

推薦度：

點擊下載本文 文檔為doc格式

導讀oracle行列互轉常用小技巧總結:平時做報表統計數據時，常遇到行列互轉，最后有總計等要求的報表式。注：下面的sql都是在oracle 10g下寫的。假如現有一張這樣的數據表： SELECT * FROM studentscores ; 多行轉多列現客戶要求這樣的式顯示這樣的式來顯示成績。方法一： WITH t

平時做報表統計數據時，常遇到行列互轉，最后有總計等要求的報表式。注：下面的sql都是在oracle 10g下寫的。假如現有一張這樣的數據表： SELECT * FROM studentscores ; 多行轉多列現客戶要求這樣的式顯示這樣的式來顯示成績。方法一： WITH t1 AS( SEL

平時做報表統計數據時，常遇到行列互轉，最后有總計等要求的報表格式。

注：下面的sql都是在oracle 10g下寫的。

假如現有一張這樣的數據表：

SELECT * FROM studentscores ;

多行轉多列

現客戶要求這樣的格式顯示

這樣的格式來顯示成績。

方法一：

WITH t1 AS(
 SELECT st1.name NAME,st1.score chinese ,0 math, 0 english
 FROM studentscores st1
 WHERE st1.object = '語文'
),
t2 AS(
 SELECT st2.name NAME,0 chinese ,st2.score math, 0 english
 FROM studentscores st2
 WHERE st2.object = '數學'
),
t3 AS(
 SELECT st3.name NAME,0 chinese ,0 math, st3.score english
 FROM studentscores st3
 WHERE st3.object = '英語'
)
SELECT t.name 姓名, SUM(t.chinese) 語文, SUM(t.math) 數學, SUM(t.english) 英語
FROM(
 SELECT t1.name,t1.chinese,t1.math,t1.english
 FROM t1
 UNION ALL
 SELECT t2.name,t2.chinese,t2.math,t2.english
 FROM t2
 UNION ALL
 SELECT t3.name,t3.chinese,t3.math,t3.english
 FROM t3
)t。
GROUP BY t.name

這種方法易理解，但是麻煩，如果學科有非常多的時候，那就麻煩到要命了。

方法二

SELECT st.NAME 姓名,
SUM(DECODE(st.object,'語文',st.score,0)) 語文,
SUM(DECODE(st.object,'數學',st.score,0)) 數學,
SUM(DECODE(st.object,'英語',st.score,0)) 英語
FROM studentscores st
GROUP BY st.NAME ;

方法二對于新手來說，也許不能一下子弄明白，不妨這樣來想想。如果我要求你統計每個學生的總分，你是不是很快就可以寫出來了select st.name,sum(st.score) from studentscores group by name.那么在這條語句的基礎上，我改一改select st.name,sum(decode(st.object,'語文',st.score,0)) 語文 from studentscores st.是不是就只選擇語文了，呵呵

多行轉一列

現在要求這樣的顯示格式：

多行轉成一列的字符串，這讓我們很容易想到oralce中的wm_concat函數的使用

SELECT st.name , wm_concat(st.object || '(' || st.score || ')')
FROM studentscores st
GROUP BY st.name;

一列轉多行

SELECT regexp_substr(t.str,'[^,]+',1,ROWNUM) VALUE
FROM(SELECT 'a,b,c,d' str FROM dual) t
CONNECT BY ROWNUM <= LENGTH(regexp_replace(t.str,'[^,]+'))+1;

可看我的另一篇博文oracle實現類型split效果

列合計

需求效果圖：

方法一

WITH t1 AS(
 SELECT st.NAME 姓名,
 SUM(DECODE(st.object,'語文',st.score,0)) 語文,
 SUM(DECODE(st.object,'數學',st.score,0)) 數學,
 SUM(DECODE(st.object,'英語',st.score,0)) 英語
 FROM studentscores st
 GROUP BY st.NAME 
 ORDER BY st.name DESC 
),
t2 AS(
 SELECT '合計' 姓名,
 SUM(DECODE(st.object,'語文',st.score,0)) 語文,
 SUM(DECODE(st.object,'數學',st.score,0)) 數學,
 SUM(DECODE(st.object,'英語',st.score,0)) 英語
 FROM studentscores st
)
SELECT t1.*
FROM t1
UNION ALL
SELECT t2.*
FROM t2

使用方法一體現不出高手風范，來看看方法二

方法二

SELECT decode(grouping(st.NAME),1,'總計',st.name) 姓名,
 SUM(DECODE(st.object,'語文',st.score,0)) 語文,
 SUM(DECODE(st.object,'數學',st.score,0)) 數學,
 SUM(DECODE(st.object,'英語',st.score,0)) 英語
FROM studentscores st
GROUP BY ROLLUP(st.NAME) 
ORDER BY st.name

呵呵，是不是覺得高深莫測！！！

這需要你先弄明白rollup,grouping是干嘛的才行，具體細節(jié)可參考我的另一篇博文oralce之rollup&grouping

在這簡單說說,group by rollup(st.name)<=>

select * from xxx group by st.name

union all

select * from xxx

就像方法一那樣，先group by st.name得到t1,然后不需要group by求總的得到t2。

GROUPING函數可以接受一列，該列必須是group by中出現的，返回0或者1。如果列值為空，那么GROUPING()返回1；如果列值非空，那么返回0。

行合計

效果圖如下：

學習了上面的行轉列之后，也許你馬上就想到這樣子來

方法一：

WITH st AS(
 SELECT st1.name,st1.object,st1.score
 FROM studentscores st1
 
 UNION ALL
 
 SELECT st2.name,'總計',SUM(st2.score)
 FROM studentscores st2
 GROUP BY st2.name
)
SELECT st.NAME 姓名,
SUM(DECODE(st.object,'語文',st.score,0)) 語文,
SUM(DECODE(st.object,'數學',st.score,0)) 數學,
SUM(DECODE(st.object,'英語',st.score,0)) 英語,
SUM(DECODE(st.object,'總計',st.score,0)) 總計
FROM st
GROUP BY st.NAME ;

嗯，這樣也是不失為一張方法，但是也不用這么麻煩

方法二

SELECT st.NAME 姓名,
SUM(DECODE(st.object,'語文',st.score,0)) 語文,
SUM(DECODE(st.object,'數學',st.score,0)) 數學,
SUM(DECODE(st.object,'英語',st.score,0)) 英語,
SUM(st.score) 總計
FROM studentscores st
GROUP BY st.NAME ;

行列合計

SELECT decode(grouping(st.NAME),1,'總計',st.name) 姓名,
 SUM(DECODE(st.object,'語文',st.score,0)) 語文,
 SUM(DECODE(st.object,'數學',st.score,0)) 數學,
 SUM(DECODE(st.object,'英語',st.score,0)) 英語,
 SUM(st.score) 合計
FROM studentscores st
GROUP BY ROLLUP(st.NAME) 
ORDER BY st.name