【Oracle】listagg函数使用及其在11g版本上的潜藏坑

listagg能把非分组列的信息归并一起，能在分组查询语句中和count,avg,sum及分组列并列查询，也算一绝。其基本用法如下：

建表：

create table emp6(
    id number(8),
    name nvarchar2(20),
    vocation nvarchar2(20),
    primary key(id)
)

充值：

insert into emp6(id,name,vocation) values(1,'andy','worker');
insert into emp6(id,name,vocation) values(2,'bill','worker');
insert into emp6(id,name,vocation) values(3,'cindy','worker');
insert into emp6(id,name,vocation) values(4,'douglas','doctor');
insert into emp6(id,name,vocation) values(5,'eliot','doctor');
insert into emp6(id,name,vocation) values(6,'felix','nurse');

现在想按职业分组，有想知道哪个职业有哪些人，listagg函数正好派上用场：

select count(*) as cnt,vocation,listagg(name,',') within group(order by 1) as names from emp6 group by vocation 

执行结果：

SQL> select count(*) as cnt,vocation,listagg(name,',') within group(order by 1) as names from emp6 group by vocation ;

       CNT VOCATION             NAMES
---------- -------------------- ----------------------------------------
         2 doctor               douglas,eliot
         3 worker               andy,bill,cindy
         1 nurse                felix

这是在oracle19c上的效果，没啥毛病。

但是，在11g上就是这个效果：

SQL> select count(*) as cnt,vocation,listagg(name,',') within group(order by 1) as names from emp6 group by vocation;

       CNT VOCATION
---------- ----------------------------------------
NAMES
----------------------------------------
         2 doctor
 d o u g l a s, e l i o t

         1 nurse
 f e l i x

         3 worker
 a n d y, b i l l, c i n d y

而且，出来的结果里的空白字符，用java的Character.isWhiteSpace是区别不出来的，用正则表达式\\s+也没有用...

要解决也很简单，用to_char函数将name字段包一下就好了，如下：

select count(*) as cnt,vocation,listagg(to_char(name),',') within group(order by 1) as names from emp6 group by vocation

执行效果：

SQL> select count(*) as cnt,vocation,listagg(to_char(name),',') within group(order by 1) as names from emp6 group by vocation;

       CNT VOCATION             NAMES
---------- -------------------- ----------------------------------------
2             doctor               douglas,eliot
1             nurse                felix
3             worker               andy,bill,cindy

END