LISTAGG查询"ORA-00937:不是单组群组功能". [英] LISTAGG Query "ORA-00937: not a single-group group function"

问题描述

我正在尝试创建一个用逗号分隔值列表的联接.例如:

I'm trying to create a join with a list of comma separated values. For example:

rule_id | attribute_id
----------------------
1       | a
1       | b
2       | c
2       | d

应为:

rule_id | attribute_id
----------------------
1       | a,b
2       | c,d

我正在尝试使用LISTAGG这样做.但是，使用下面的代码，我得到了ORA-00937: not a single-group group function.我注意到sql-server的FOR PATH语法，但是对于我们的配置而言，它看起来不起作用.这是我的查询:

I am attempting to do so using LISTAGG. However, with the below code, I'm getting ORA-00937: not a single-group group function. I noticed a FOR PATH syntax for sql-server, but it doesn't look like that works for our configuration. Here's my query:

SELECT r.rule_id as RULE_ID, 
LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) "ATTR_IDS"
FROM N_RULE r, N_ATTRIBUTE a 
WHERE r.RULE_ID = a.RULE_ID 
ORDER BY r.AUDIENCE, UPPER(r.NAME);

推荐答案

我认为，要使您的查询正常工作，您需要添加group by，更改order by.您还应该使用适当的显式连接语法:

I think for your query to work, you need to add a group by, change the order by. You should also use proper explicit join syntax:

SELECT r.rule_id as RULE_ID, 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id
ORDER BY r.rule_id;

或者，可能要在结果中包括其他属性:

Or, possibly you want to include other attribute in the results:

SELECT r.rule_id, r.AUDIENCE, UPPER(r.NAME) 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id, r.AUDIENCE, UPPER(r.NAME)
ORDER BY r.AUDIENCE, UPPER(r.NAME);

这篇关于LISTAGG查询"ORA-00937:不是单组群组功能".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！