今天在做项目,讲mysql项目转换为oracle项目的时候,发现某些sql语句不兼容
mysql语句如下
SELECT COLUMN1, group_concat(SHOUYILV ORDER BY CRETE_DATE) AS SHOUYILV FROM RCONFIGDATA WHERE column2 = '1' GROUP BY COLUMN1 ORDER BY COLUMN1
发现上面语句在oracle下不识别group_concat函数,因此需要进行转换,转换语句如下
SELECT COLUMN1,listagg(SHOUYILV,',') WITHIN GROUP(ORDER BY CRETE_DATE) AS SHOUYILV FROM RCONFIGDATA WHERE column2 = '1' GROUP BY COLUMN1 ORDER BY COLUMN1
各个函数详解
wm_concat
该方法来自wmsys下的wm_concat函数,属于Oracle内部函数,返回值类型varchar2,最大字符数4000。随着版本的变更返回值类型可能会有改动,项目中使用时候最好在新的用户下创建一个函数。
使用方法:
select deptno,wm_concat(ename) from emp group by deptno;
排序方法(未必仅此一种写法):
select *
from (select wm_concat(ename) over(partition by deptno order by empno) val,
row_number() over(partition by deptno order by empno desc) rn,
a.*
from emp a)
where rn = 1;
如果仅是简单聚合数据,可以使用该函数,
优点:效率高。
缺点:
(1)、返回最大字符数4000;
(2)、行数据默认以逗号分隔,可以修改函数更改,但是函数一旦创建不能随意自定义分隔符;
(3)、排序实现复杂且效率低;
(4)、内部聚合混乱。比如:
select wm_concat(col1) col3,wm_concat(col2) col4 from tab;
返回的col3和col4里的聚合数据未必是一一对应的。
zh_concat
该函数是在wm_concat基础上修改返回值类型得到,可以返回clob类型数据,内部实现同wm_concat。优缺点同wm_concat。
listagg
11g新增函数,返回值varchar2,同样受4000字符数限制。但是可以排序,可以指定分隔符。
使用方法:
select deptno,listagg(ename,',') within group(order by empno) from emp group by deptno
优点:
(1)、可排序
(2)、可自定义分隔符
缺点:
(1)、仅11g之后版本可用
(2)、返回最大字符数4000
xmlagg
该方法通过将数据聚合成xml结构,再转换成varchar2或者clob类型。
使用方法:
select deptno,xmlagg(xmlparse(content ename||',' wellformed) order by empno).getstringVal() from emp group by deptno;
select deptno,xmlagg(xmlparse(content ename||',' wellformed) order by empno).getclobval() from emp group by deptno;
优点:
(1)、可排序
(2)、可返回clob类型容纳大数据量数据
(3)、可自定义分隔符
(4)、10g可用
缺点:
(1)、在不排序的情况下效率比wm_concat、zh_concat差
(2)、在排序情况下效率比listagg差
(3)、最终数据在后面或者前面会多一个分隔符,需要再做处理
sys_connect_by_path
借助connect by实现数据聚合。
实现方法:
select deptno, res
from (select rn, level, deptno, sys_connect_by_path(ename, ',') res,
connect_by_isleaf il
from (select row_number() over(partition by deptno order by empno) rn,a.*
from emp a)
start with rn = 1
connect by deptno = prior deptno
and prior rn = rn - 1)
where il = 1
该方法实现复杂,效率低下,这里不再讨论。
总结
不同场景下使用不同方法(最佳选择):
|
10g |
11g以上 |
排序(varchar2) |
xmlagg |
listagg |
排序(clob) |
xmlagg |
xmlagg |
不排序(varchar2) |
wm_concat |
wm_concat |
不排序(clob) |
zh_concat |
zh_concat |
相关推荐
oracle最全函数大全(分析函数-聚合函数-转换函数-日期型函数-字符型函数-数值型函数-其他函数
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。
8.4.1 跨越整个分区的聚合函数 201 8.4.2 细粒度窗口声明 201 8.4.3 默认窗口声明 202 8.5 Lead和Lag 202 8.5.1 语法和排序 202 8.5.2 例1:从前一行中返回一个值 203 8.5.3 理解数据行的位移 204 8.5.4 例2...
1:在进行多表关联时,多用 Where 语句把单个表的结果集最小化,多用聚合函数汇总结果集后再与其它表做关联,以使结果集数据量最小化2:在两张表进行关联时,应考虑可否使用右连接。以提高查询速度3:使用 where 而...
学习过 SQL 的人都知道,使用聚合函数(AVG、SUM、COUNT、MIN/MAX 等)和分组操作(GROUP BY)可以对数据进行基本的统计分析,例如统计公司员工的人数、每个部门的平均月薪等。如果想要回顾这些基础概念,可以参考这...
4.3.2 数据分组与聚合函数 4.3.3 HAVING语句 4.4 限制结果集行数 4.4.1 MySQL 4.4.2 MS SQL Server 2000 4.4.3 MS SQL Server 2005 4.4.4 Oracle 4.4.5 DB2 4.4.6 数据库分页 4.5 抑制数据重复 ...
4.3.2 数据分组与聚合函数 4.3.3 HAVING语句 4.4 限制结果集行数 4.4.1 MySQL 4.4.2 MS SQL Server 2000 4.4.3 MS SQL Server 2005 4.4.4 Oracle 4.4.5 DB2 4.4.6 数据库分页 4.5 抑制数据重复 ...
4.3.2 数据分组与聚合函数 4.3.3 HAVING语句 4.4 限制结果集行数 4.4.1 MySQL 4.4.2 MS SQL Server 2000 4.4.3 MS SQL Server 2005 4.4.4 Oracle 4.4.5 DB2 4.4.6 数据库分页 4.5 抑制数据重复 ...
4.3.2 数据分组与聚合函数 4.3.3 HAVING语句 4.4 限制结果集行数 4.4.1 MySQL 4.4.2 MS SQL Server 2000 4.4.3 MS SQL Server 2005 4.4.4 Oracle 4.4.5 DB2 4.4.6 数据库分页 4.5 抑制数据重复 ...
4.3.2 数据分组与聚合函数 4.3.3 HAVING语句 4.4 限制结果集行数 4.4.1 MySQL 4.4.2 MS SQL Server 2000 4.4.3 MS SQL Server 2005 4.4.4 Oracle 4.4.5 DB2 4.4.6 数据库分页 4.5 抑制数据重复 ...
第1章 窗体与界面设计 1 1.1 菜单应用实例 2 实例001 带历史信息的菜单 2 实例002 菜单动态合并 3 实例003 像开始菜单一样漂亮的菜单 4 实例004 任务栏托盘菜单 5 实例005 可以拉伸...
一部久享盛誉的程序开发宝典。精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后...
一部久享盛誉的程序开发宝典。精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后...
一部久享盛誉的程序开发宝典。精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后...
一部久享盛誉的程序开发宝典。精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后...