oracle内置函数 wmsys.wm_concat使用

来源：互联网发布：仁化网络问政编辑：程序博客网时间：2024/06/06 23:50

函数名：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;