oracle LISTAGG 查询“ORA-00937：不是单组组函数”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24253262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LISTAGG Query "ORA-00937: not a single-group group function"
提问by kb_
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
should be:
应该：
rule_id | attribute_id
----------------------
1 | a,b
2 | c,d
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 PATHsyntax for sql-server, but it doesn't look like that works for our configuration. Here's my query:
我正在尝试使用LISTAGG. 但是，使用下面的代码，我得到了ORA-00937: not a single-group group function. 我注意到FOR PATHsql-server的语法，但它看起来不适用于我们的配置。这是我的查询：
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);
回答by Gordon Linoff
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:
我认为要使您的查询正常工作，您需要添加一个group by，更改order by. 您还应该使用正确的显式连接语法：
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);
