开发避坑指南(31)：Oracle 11g LISTAGG函数使用陷阱，缺失WITHIN子句解决方案

错误信息

Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-02000: 缺失 WITHIN 关键字

查询语句

使用LISTAGG函数将多行数据合并为单行字符串，如下：

select
    t.order_no as orderNo,
    t.account_no,
    (select listagg(a.bank_name,',')  from t_account_info a 
        where t.account_no = a.account_no and a.account_type = '01') as bankName,
    t.payer_name as payername
from t_order_info t
where 1=1
and t.order_no is not null;

错误分析

报以上错误的问题是oracle的版本和listagg的关键字使用不对应，当前用的是oracle 11g ，该版本的listagg使用需要在后面加上within group(order by 字段)，如果是oracle 19c的话可以直接使用listagg(字段,‘,’)。

解决办法

将查询语句修改为如下：

select
		t.order_no as orderNo,
		t.account_no,
		(select listagg(a.bank_name,',') within group (order by a.bank_name) from t_account_info a 
			where t.account_no = a.account_no and a.account_type = '01') as bankName,
		t.payer_name as payername
		
from t_order_info t
where 1=1
and t.order_no is not null;