函数名:wmsys.wm_concat
作用:以逗号分隔连接列的值
oracle 10g引入。使用时直接用wm_concat也可以。
以下介绍一些实际使用的状况。介绍实际Case前先建立一个测试table和一些测试数据。
两张表: employee,prject;属性如下
1)employee: 员工ID号,员工名,部门
测试数据
0001 user1 IT
0002 user2 IT
2)prject:项目ID号,负责的员工ID号
测试数据
PN001 0001
PN002 0001
PN003 0001
PN004 0001
PN010 0002
PN011 0002
对应的SQL如下
create table employee
(
ID varchar(4) NOT NULL PRIMARY KEY,
NAME varchar(20),
DEPT varchar(20)
);
create table prject
(
PROJECTID varchar(5) NOT NULL PRIMARY KEY,
OWNERID varchar(20)
);
INSERT INTO employee(ID,NAME,DEPT) Values('0001','user1','IT');
INSERT INTO employee(ID,NAME,DEPT) Values('0002','user2','IT');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN001','0001');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN002','0001');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN003','0001');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN004','0001');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN010','0002');
INSERT INTO prject(PROJECTID,OWNERID) Values('PN011','0002');
Case 1: 列转换行。 以一行显示所有员工的名字
select wmsys.wm_concat(NAME) from employee;
结果: user1,user2
Case 2: join 两张table , 计算员工负责的 项目个数的例子.
select t1.ID,t1.DEPT,t2.pcount from
(select ID,NAME,DEPT from employee) t1 left outer join
(select OWNERID,trunc(length(replace(wm_concat(PROJECTID),',',''))/5) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;
结果:
0001 IT 4
0002 IT 2
此Case如果使用Count替代的话也可以,而且写法更简单,但是table很复杂的时候使用count不能达成时,可以考虑这个方式, 此处附上count方式
select t1.ID,t1.DEPT,t2.pcount from
(select ID,NAME,DEPT from employee) t1 left outer join
(select OWNERID,count(PROJECTID) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;
分享到:
相关推荐
Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数,若用到此函数, 系统会提示异常:ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数,方法如下: 解压附件,在sqlplus中执行包里的owmctab....
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
大家用oracle数据库里面的wmsys.wm_concat函数有没有遇见过字符串缓冲区太小的情况? 针对这种情况我写了个自定义聚合函数 是clob版的。
重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。 简单来说,用PL/SQL执行下一下几个脚本就可以了。 特别要注意:PL/SQL登录时,要...
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。
行转列函数,对于学习sql查询的朋友们,可能会遇到这样的要求,此文档中做了详细记载,希望可以帮到你!
* 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100,可拼出超4000的超长字符串 * 可自定义指定分隔符separator * 可自定义指定排序字段sequence,对于数字或日期类型的...
解决方案:使用Oracle自带的函数 WMSYS.WM_CONCAT,进行拼接。 函数限制:它的输出不能超过4000个字节。 为了不让SQL出错,又可以满足业务的需求,超过4000个字节的部分,使用“。。。” 实现SQL如下: 代码如下: ...
1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb ...如果执行结果报错,说找不到WMSYS用户,那么执行 @C:\Users\JOYTRAVEL\Desktop\WMSYS用户\owmctab.plb; 再执行owmaggrb和owmaggrs
包含owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 三个文件,可解决OracleXE中没有WM_CONCAT函数的问题;解决方案如下:1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录:...
一、Oracle 10G以前使用WMSYS.WM_CONCAT: wmsys.wm_concat将字段的值用”,”来隔开。 select id,wm_concat(name) from tab_name group by id; 二、使用sys_connect_by_path sys_connect_by_path(字段名, 2个字段...
Oracle 重建WMSYS用户的WMSYS.WM_CONCAT函数
Oracle 12c 创建建WMSYS用户的WMSYS.WM_CONCAT函数 解决12c不支持WM_CONCAT函数问题 解压到E:\WMSYS\目录下然后在cmd里执行下边的命令 sqlplus / as sysdba @E:\WMSYS\owmctab.plb; @E:\WMSYS\owmaggrs.plb; @...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
Oracle 11g 重建WMSYS用户的WMSYS.WM_CONCAT函数,WMSYS用户