Oracle11g SQL新功能pivot/unpivot
来源:互联网 发布:如何去痘印 知乎 编辑:程序博客网 时间:2024/05/01 09:13
领袖又说了:“温故而知新”。那就让我们先看看11g以前是怎么实现地。行列转换一直当作甄别老手和新手的试金石,面试的时候面试官不问这个都不好意思张嘴。Itpub的Oracle开发版更是每隔十天半个月就有人问这个,你说重要不重要。
假设有表emp_phone如下:
表里放着张三李四王二麻子等等主人翁的电话号码。(TYPE 1/2/3分别对应家/办公室/手机)。如果要把每个人的所有电话放在一行上,就是行转列了。结果如下:
写这个SQL的技巧就是按姓名分组,然后使每一组每一类的电话号码最多只有一个,里边用到的分组函数都是聋子的耳朵-摆设。用MAX可以,MIN也行。
这个查询写出来就是:
SELECTname,
MAX(decode(type, 1, phone)) Home,
MAX(decode(type, 2, phone)) Office,
MAX(decode(type, 3, phone)) Mobile
FROM
emp_phone
GROUP BY
Name
/
那位看官说了:“能不能再变回去?”能,不能戏法不就漏了不是?
这儿要用到另一的技巧就是笛卡尔乘积,将一行复制成三行,每一行取一个类型的电话
偷个懒儿把上边的结果表叫emp_phone_x,把列还原成行的SQL:
SELECTNAME,
DECODE (lvl, 1, home, 2, office, 3, mobile) phone
FROM
emp_phone_x,
(SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= 3)
WHERE
DECODE (lvl, 1, home, 2, office, 3, mobile) IS NOT NULL /2. 11g 自带的行列转换
旁边那个带眼镜,说的就是你,眼珠子直勾勾的怎么了?上面的没看懂? 要是以前,我老先生就得语重心长地教育你,那么重要的东西没看懂,将来想不想换工作了?但现在这话就说不出口了,因为11g的SQL自己就带这个了。
11g在SELECT语句中新加了关键词PIVOT和UNPIVOT,用这两个关键词,重写上面的两个查询,就变成这个样子的了:
行变列:
SELECT * FROM emp_phonePIVOT (
MAX(phone) for type IN (1 as home, 2 as office, 2 as mobile)
)
/
PIVOT以后的字句都是新加的。但万变不离其宗,还是要用到分组函数。IN后边是按type的不同值映射成不同的列。简单吧?
列变行,这是UNPIVOT的工作,写法如下:
SELECT * FROM emp_phone_xUNPIVOT (
phone FOR type in (HOME AS 1, OFFICE AS 2, MOBILE AS 3)
)
/
这里是把不同的列转换成不同的type的数值。
再用SCOTT用户里的EMP表做个例子,列出各部门之间工资总和:
SELECT * FROM(
(SELECT sal, deptno FROM emp)
PIVOT (
SUM(sal) FOR deptno IN (10 as dept_10, 20 as dept_20, 30 as dept_30)
)
)
/
DEPT_10 DEPT_20 DEPT_30
---------- ---------- ----------
8750 10875 9400
再往深里想,前边的所有例子都有一个局限,电话的type和emp的deptno都是有限的、可穷举的。如果这些列都是可随时可添加的,又该怎么办呢?11g以前肯定是要动用动态SQL的法宝。那11g又是怎么处理的呢?刚看SQL参考手册的时候,看到里边豁然写着IN后边可以接子查询或ANY,当时是佩服的眼泪哗哗的,迫不及待赶紧试一试:
SELECT * FROM(
(SELECT sal, deptno FROM emp)
PIVOT (
SUM(sal) FOR deptno IN (SELECT deptno FROM dept)
)
)
/
ERROR at line 5:
ORA-00936: missing expression
SELECT * FROM
(
(SELECT sal, deptno FROM emp)
PIVOT (
SUM(sal) FOR deptno IN (ANY)
)
)
/
ERROR at line 5:
ORA-00936: missing expression
这一下又变成拔凉拔凉的,这么大个ORACLE也不能无耻到这个地步吧?正准备再确认一下手册,抓他个人赃俱获,突然有发现里边豁然写着: A subquery is used only in conjunction with the XML keyword…
The ANY keyword is used only in conjunction with the XML keyword…
学习不认真,该打。原来是给生成XML串用的,正确用法如下:
SELECT * FROM(
(SELECT sal, deptno FROM emp)
PIVOT XML (
SUM(sal) FOR deptno IN (ANY)
)
)
<PivotSet>
<item>
<column name = "DEPTNO">10</column>
<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>
- Oracle11g SQL新功能pivot/unpivot
- Oracle11g SQL新功能pivot/unpivot
- oracle-行转列 oracle11g sql新功能pivot/unpivot
- oracle-行转列点评oracle11g sql新功能pivot/unpivot
- SQL pivot unpivot
- sql PIVOT,UNPIVOT,GROUPING SETS
- sql server行转列 Pivot UnPivot
- Pivot 与 Unpivot 详解 - SQL
- SQL行列转换PIVOT、UNPIVOT
- sql server 行转列 Pivot UnPivot
- PIVOT & UNPIVOT
- PIVOT & UNPIVOT
- SQL Server2005的PIVOT/UNPIVOT行列转
- SQL Server 2005中PIVOT與UNPIVOT
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- 要开始自己写一些算法了
- nginx ssl的安装和配置
- ppk和pem文件互转
- 基于 Android NDK 的学习之旅-----Android.mk 介绍
- Sql Server 2005 数据库备份还原后出现“受限制用户”问题的解决
- Oracle11g SQL新功能pivot/unpivot
- IPV6相关知识
- 配置CDT+MinGW遇到的问题及解决方法
- 三层架构深入认识(二)
- MySQL 5.0 5.1 下InnoDB的预热方法
- 多台Mac电脑使用一个apple开发者账号
- ruby入门_类变量
- javascript 显示年月(传参)
- java中获取文件路径的几种方式