通过Oracle串联 [英] Concatenate Over Oracle
问题描述
这是样本表数据
Fruit Number
Apple 1
Apple 2
Apple 3
Kiwi 6
Kiwi 10
我尝试将表的列值连接起来以获取以下信息
I try to concatenate the table column values to get the following
Fruit Number
Apple 1-2-3
Kiwi 6-10
是否有查询此或存储过程的方法? 像Concatenate over(partition by)之类的东西,我对存储过程了解不多.谢谢!
Is there a way to query this or store procedure? Something like Concatenate over(partition by) , I don't know much about stored procedures. Thanks!
推荐答案
OP在Oracle 10g 上运行,并且在 11g第2版中引入了 LISTAGG strong>.
OP is on Oracle 10g, and LISTAGG was introduced in 11g Release 2.
因此,在不支持LISTAGG
的Oracle版本 11g 之前,可以使用 ROW_NUMBER()和 SYS_CONNECT_BY_PATH 函数.
Therefore, in Oracle version prior to 11g where LISTAGG
is not supported, you could use ROW_NUMBER() and SYS_CONNECT_BY_PATH functions.
SELECT fruit,
LTRIM(MAX(SYS_CONNECT_BY_PATH(number,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
FROM (SELECT fruit,
number,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) -1 AS prev
FROM table_name)
GROUP BY fruit
CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit
START WITH curr = 1;
注意
请勿使用WM_CONCAT
,因为它是未记录的功能,并且已从12c版本中删除.
Never use WM_CONCAT
since it is an undocumented feature and it has been removed from 12c version.
任何依赖于wm_concat
功能的应用程序一旦升级到12c
,将无法正常工作.从那以后,它已被删除.请参见为什么不在Oracle中使用WM_CONCAT函数?
Any application which has had been relying on wm_concat
function will not work once upgraded to 12c
. Since, it has been removed. See Why not use WM_CONCAT function in Oracle?
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>
您将收到"无效的标识符"错误:
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 feature which is no more made available in latest versions.
这篇关于通过Oracle串联的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!