转自 http://tech.it168.com/db/2007-09-07/200709071114985.shtml
【IT168技术文档】
摘要:(简要介绍Oracle11g SQL的新功能 pivot/unpivot 的使用方法以及如何使用它们做到行列转换.
蓄势以久的Oracle 11g 终于七月敲锣打鼓隆重推出,接下来就是网上漫天盖地的新功能介绍。11g面向开发的新功能本来就不多,掰着手指头也就是pivot和查询结果缓存的新Hint。本以为不久就会有人详述,谁知盼到两眼欲穿,大家还是翻来覆去的讨论DBA的自动分区之类。Oracle自己的门脸上到是每每用客气的冷漠写着“马上就来” (coming soon),可这马上都转眼都快马上了一个月了,还迟迟不见盖头掀起来。
(http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html?rssid=rss_otn_articles)
伟人说过“自己动手,丰衣足食”,等不来,咱就自己来。没吃过猪肉,还没见过猪跑?说干就干,下载安装再加一本“SQL参考手册”,齐了。这新花活到底怎么使,且听我从头道来。。。
1. 11g以前的行列转换
领袖又说了:“温故而知新”。那就让我们先看看11g以前是怎么实现地。行列转换一直当作甄别老手和新手的试金石,面试的时候面试官不问这个都不好意思张嘴。Itpub的Oracle开发版更是每隔十天半个月就有人问这个,你说重要不重要。
假设有表emp_phone如下:
NAME
|
TYPE
|
PHONE
|
张三
|
1
|
1234-5678
|
张三
|
2
|
4567-7890
|
张三
|
3
|
6000-1001
|
李四
|
1
|
2123-1237
|
李四
|
3
|
6001-5600
|
马五u
|
1
|
3248-1378
|
马五
|
2
|
3423-3948
|
王二(没麻子)
|
2
|
2890-1245
|
。。。
|
|
|
表里放着张三李四王二麻子等等主人翁的电话号码。(TYPE 1/2/3分别对应家/办公室/手机)。如果要把每个人的所有电话放在一行上,就是行转列了。结果如下:
NAME
|
HOME
|
OFFICE
|
MOBILE
|
张三
|
1234-5678
|
4567-7890
|
6000-1001
|
李四
|
2123-1237
|
|
6001-5600
|
马五
|
3248-1378
|
3423-3948
|
|
王二(没麻子)
|
|
2890-1245
|
这个查询写出来就是:
那位看官说了:“能不能再变回去?”能,不能戏法不就漏了不是?
这儿要用到另一的技巧就是笛卡尔乘积,将一行复制成三行,每一行取一个类型的电话
偷个懒儿把上边的结果表叫emp_phone_x,把列还原成行的SQL:
转来转去,一来一往,阴阳辟易,详推用意终何在,延年益寿不老春。往玄里说,就是老祖宗老挂在嘴边上的“道”。那位又说了:“这都哪儿跟哪儿啊?怎么扯到太极拳上去了”。
2. 11g 自带的行列转换 旁边那个带眼镜,说的就是你,眼珠子直勾勾的怎么了?上面的没看懂? 要是以前,我老先生就得语重心长地教育你,那么重要的东西没看懂,将来想不想换工作了?但现在这话就说不出口了,因为11g的SQL自己就带这个了。
11g在SELECT语句中新加了关键词PIVOT和UNPIVOT,用这两个关键词,重写上面的两个查询,就变成这个样子的了:
行变列:
SELECT * FROM emp_phone
PIVOT (
MAX(phone) for type IN (1 as home, 2 as office, 2 as mobile)
)
/
PIVOT以后的字句都是新加的。但万变不离其宗,还是要用到分组函数。IN后边是按type的不同值映射成不同的列。简单吧?
列变行,这是UNPIVOT的工作,写法如下:
再往深里想,前边的所有例子都有一个局限,电话的type和emp的deptno都是有限的、可穷举的。如果这些列都是可随时可添加的,又该怎么办呢?11g以前肯定是要动用动态SQL的法宝。那11g又是怎么处理的呢?刚看SQL参考手册的时候,看到里边豁然写着IN后边可以接子查询或ANY,当时是佩服的眼泪哗哗的,迫不及待赶紧试一试:
这一下又变成拔凉拔凉的,这么大个ORACLE也不能无耻到这个地步吧?正准备再确认一下手册,抓他个人赃俱获,突然有发现里边豁然写着:
< column name = " SUM (SAL)" > 8750 column > item >< item >
< column name = "DEPTNO" > 20 column >< column name = " SUM (SAL)" > 10875 column > item >< item >
< column name = "DEPTNO" > 30 column >< column name = " SUM (SAL)" > 9400 column > item > PivotSet >
这个东西的结果具体怎么用就留给大家做作业了。反正XML咱也不熟,借这个机会就下了。。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67798/viewspace-1042075/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/67798/viewspace-1042075/