ORACLE/SQL：wm_concat & order by
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4970477/
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
ORACLE/SQL: wm_concat & order by
提问by Christian M
I'm using oracle 11 (not sure about the exact version, but since LISTAGG doesn't work, I suppose it's not release 2) through ODBC and crystal reports 2008.
我正在通过 ODBC 和 Crystal Reports 2008 使用 oracle 11（不确定确切版本，但由于 LISTAGG 不起作用，我想它不是第 2 版）。
Here is the problem I have:
这是我遇到的问题：
Here's a table:
这是一个表格：
TABLE ODB.TASK_CARD_CONTROL
------------------------------------------
task_card control_category code
------------------------------------------
1 zone 17
1 zone 33
1 zone 21
2 zone 18
2 zone 05
3 zone 55
3 zone 32
3 zone 72
I'm using the WM_CONCAT function to obtain something like this:
我正在使用 WM_CONCAT 函数来获得这样的东西：
task_card zones
1 17,33,21
2 18,05
3 55,32,72
Here is the SQL for that:
这是 SQL：
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD
but I'd like the zones to be sorted, so I tried this:
但我希望对区域进行排序，因此我尝试了以下操作：
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL
ORDER BY CODE)
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD
but for some reason, it returns the following error:
但由于某种原因，它返回以下错误：
Failed to retrieve data from the database.
Details: 42S22:[Oracle][ODBC][Ora]ORA-00904:
"ODB"."TASK_CARD_CONTROL"."CONTROL_CATEGORY" : invalid identifier
I really don't understand what I'm doing wrong here... Can anybody give me a hint ?
我真的不明白我在这里做错了什么......有人可以给我一个提示吗？
采纳答案by Tony Andrews
You can't reference ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY from outside the inner query. Try:
您不能从内部查询外部引用 ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY。尝试：
SELECT TASK_CARD, WM_CONCAT(code) as ZONES
FROM (SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
ORDER BY CODE)
GROUP BY TASK_CARD
回答by Alexandru Simionov
For anyone that is still using wm_CONCAT (a.k.a. older db versions): The solution is to add distinct condition, it will then also apply ascending order to the concatenated values.
对于仍在使用 wm_CONCAT（又名较旧的 db 版本）的任何人：解决方案是添加不同的条件，然后它将升序应用于连接的值。
Don't ask why it's not documented, but it will work.
不要问为什么它没有记录，但它会起作用。
Also, using a order by in a subquery, previous to wm_concat will just randomize the order, so it shouldn't have been recommended.
此外，在子查询中使用 order by 在 wm_concat 之前只会随机化顺序，因此不应该推荐它。
Example for the requested SQL:
请求的 SQL 示例：
SELECT TASK_CARD, WM_CONCAT(distinct code) as ZONES
FROM ODB.TASK_CARD_CONTROL
WHERE ODB.TASK_CARD_CONTROL.CONTROL_CATEGORY = 'ZONE'
GROUP BY TASK_CARD;
Just be warned that the distinct option does not work when used in procedures/packages .
请注意，当在 processes/packages 中使用时， distinct 选项不起作用。
回答by Netflix Mumbhai
- Order by the desired column, then
- Order in external query order by row number.
- Use the function.
- 按所需的列排序，然后
- 按行号按外部查询顺序排序。
- 使用该功能。
This function has logic for the last rownum order:
此函数具有最后一个 rownum 顺序的逻辑：
Select wmsys.wm_concat(t) CONCAT from
(
Select t from (
Select t from (
Select 'aa' t from dual
union
Select 'zz' t from dual
union
Select 'pp' t from dual
union
Select 'll' t from dual
union
Select 'mm' t from dual
union
Select 'xx' t from dual
union
Select 'cc' t from dual
) a
order by t
) order by rownum
) t
回答by Lalit Kumar B
LISTAGGwas introduced in 11g Release 2.
LISTAGG是在11g 第 2 版中引入的。
Therefore, in Oracle version prior to 11gwhere LISTAGGis not supported, you could use ROW_NUMBER()and SYS_CONNECT_BY_PATHfunctions.
因此，在Oracle版本11克之前，那里LISTAGG是不支持，你可以使用ROW_NUMBER（）和SYS_CONNECT_BY_PATH功能。
See Oracle String Aggregation Techniques
SELECT task_card,
LTRIM(MAX(SYS_CONNECT_BY_PATH(code,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS zones
FROM (SELECT task_card,
code,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) AS curr,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY code) -1 AS prev
FROM table_name)
GROUP BY task_card
CONNECT BY prev = PRIOR curr AND task_card= PRIOR task_card
START WITH curr = 1;
NOTE
笔记
Never use WM_CONCATsince it is an undocumented feature and it has been removed from 12c version.
永远不要使用，WM_CONCAT因为它是一项未记录的功能，并且已从 12c 版本中删除。
Any application which has had been relying on wm_concatfunction will not work once upgraded to 12c. Since, it has been removed. See Why not use WM_CONCAT function in Oracle?
任何一直依赖wm_concat功能的应用程序一旦升级到12c. 从那以后，它已被删除。请参阅为什么不在 Oracle 中使用 WM_CONCAT 函数？
SQL> select banner from v$version where rownum = 1;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL> SELECT object_name
2 FROM dba_objects
3 WHERE owner='WMSYS'
4 AND object_name LIKE 'WM\_%' ESCAPE '\';
OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES
20 rows selected.
SQL>
You will receive an “invalid identifier” error:
您将收到“无效标识符”错误：
SQL> SELECT banner FROM v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
*
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier
Therefore, there is no point relying on an undocumented featurewhich is no more made available in latest versions.
因此，依赖最新版本中不再提供的未记录功能是没有意义的。
回答by Bren M
If you give the sub query in the from clause a name you can then refer to columns in the sub query itself
如果您在 from 子句中为子查询命名，则您可以引用子查询本身中的列
SELECT t1.TASK_CARD
, WM_CONCAT(t1.code) as ZONES
FROM
(SELECT TASK_CARD, CODE, CONTROL_CATEGORY FROM ODB.TASK_CARD_CONTROL ORDER BY CODE) t1
WHERE t1.CONTROL_CATEGORY = 'ZONE'
GROUP BY t1.TASK_CARD
回答by Kyle Hu
Use ListAgg instead of wm_concat
使用 ListAgg 而不是 wm_concat
SELECT TASK_CARD, ListAgg(code) within (order by code asc) as ZONES
http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html
http://nimishgarg.blogspot.com/2010/07/oracle-diferece-between-wmconcat-and.html
