作者：皇家陈小小2_328 | 来源：互联网 | 2014-07-11 17:33
Oracle行列转换的思考与总结最近几天一直在弄Oracle-SQL的问题，涉及到了一些平时没有用到的东西，也因此而在这里郁闷了好久。现在问题得到了解决虽说不算完美。但是还是和大家一起分享一下。行列转换之一：sum（...SyntaxHighlighter.all();
最近几天一直在弄Oracle-SQL的问题，涉及到了一些平时没有用到的东西，也因此而在这里郁闷了好久。现在问题得到了解决虽说不算完美。但是还是和大家一起分享一下。
行列转换之一：sum（case when.. then.. else.. end） as 语句
这种也可能是我们遇到的第一个行列转换的方法。巧妙的利用汇总和判断语句就可以解决的。
先看一个简单的基础表：如下图
create table STUDENT
( www.2cto.com
STUNAME NVARCHAR2(50),
SUBJECTNAME NVARCHAR2(50),
SUBJECTSCORE NUMBER
)
表中有两个人的成绩，每一行代表每个学生该学科的成绩。这个也是我们初学SQL时候很常见的一个表结构了。
要求：把上面的多行值以学生为单位转化为2行值，可以很直观的看出每个学生的各科成绩。于是乎便有了下面的语句。
select
s.stuname,
sum(case when s.subjectname=&＃39;语文&＃39; then s.subjectscore else 0 end ) as 语文成绩,
sum(case when s.subjectname=&＃39;数学&＃39; then s.subjectscore else 0 end ) as 数学成绩,
sum(case when s.subjectname=&＃39;英语&＃39; then s.subjectscore else 0 end ) as 英语成绩
from student s
group by s.stuname
order by s.stuname
执行后所得结果：
上面的做法也很容易让人理解，在这里就不多说了，但是上面的也是有局限性的。因为此处毕竟存在着一些固定值。比如case 中的“语文，数学。。”不过遇到小的业务需求，还是可以拿来直接用的。比如一个学生成绩管理
系统这个就可以满足了。
行列转换之二：sys_connect_by_path系统函数，自定义函数，connect by sname = prior sname and rank-1 = prior rank递归 语句 www.2cto.com
素材还是上面的表，下面我想得到的结果是：
把一个对象的各个学科的属性连接起来放入到一个列中，用字符串存放。
当时看到这个业务需求的时候，当然学生这个只是举个例子。很没有头绪。但是业务那边也急着要数据，没办法。只好硬着头皮想办法。首先就想起了这个要用连接字符串的函数，还需要判断循环之类的。于是乎就想起了这样做，创建一个自定义函数。来处理字符串不断的相加。
方法1：自定义函数，循环
CREATE OR REPLACE FUNCTION getallsubject(parameter varchar2)--有参数方法，字符串
RETURN varchar2
IS
return_str varchar2(4000);--该方法返回一个字符串。
BEGIN
FOR rs IN
(
SELECT s.subjectname||&＃39;:&＃39;||s.subjectscore as allshow
FROM student s
WHERE s.stuname=parameter--当参数一直符合条件 for循环插入结果集rs
) LOOP
return_str:=return_str||rs.allshow;--loop所有字符串，相加
END LOOP;
RETURN return_str;
END;
执行select s.stuname,getallsubject(s.stuname) from student s
可见，改方法对每一行值都进行了判断，产生了多条记录。然后select distinct s.stuname,getallsubject(s.stuname) from student s
效果：
得到了想要的结果。
方法2：利用oracle自带的sys_connect_by_path
要说明的是： www.2cto.com
所以在使用这个函数之前，我们必须先对源数据进行处理。第一步简单的处理一下。
create table stu1 as
select s.stuname sname,s.subjectname||s.subjectscore sshow from student s
--
select *from stu1
如下所示：
OK下面就可以写语句了。
select sname as 姓名,allstr 详细描述 from
(
select sname,allstr,
row_number() over(partition by sname order by sname,curr_level desc) ename_path_rank
from (
select sname,sshow,rank,level as curr_level,
ltrim(sys_connect_by_path(sshow,&＃39;,&＃39;),&＃39;,&＃39;) allstr from --把所有字符串相加
(
select s1.sname,s1.sshow,row_number() over(partition by s1.sname order by s1.sname,s1.sshow) rank www.2cto.com
from stu1 s1 order by s1.sname,s1.sshow--创建树关系，name可以作为parentid，rank可以作为childid
) connect by sname = prior sname and rank-1 = prior rank
)
)
where ename_path_rank=1;
执行结果：
也得到了我们想要的结果。当然不用学科之间的间隔符我们可以用replace函数自定义。
比较复杂一点的行列转换用以上两个方法都可以实现。但是这两个方法却都存在着一些优点和缺点。但是本人建议还是使用下面的方法比较靠谱。
作者 小人物。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 