oracle行列互转常用小技巧总结

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle行列互转常用小技巧总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4510字，纯文字阅读大概需要7分钟。

内容图文

平时做报表统计数据时，常遇到行列互转，最后有总计等要求的报表式。 注：下面的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是干嘛的才行，具体细节可参考我的另一篇博文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 

一些报表有些需要更复杂，比如每一种分类都有一个小计，最后再来一个合计，这个可学习我的另一篇博文

oracle之rollup&cube&grouping

内容总结

以上是互联网集市为您收集整理的oracle行列互转常用小技巧总结全部内容，希望文章能够帮你解决oracle行列互转常用小技巧总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---