连接甲骨文

Question

这是一个示例表数据。

Fruit   Number
Apple    1
Apple    2
Apple    3
Kiwi     6
Kiwi     10

我尝试将表列值连接起来，以获得以下内容

Fruit   Number
Apple    1-2-3
Kiwi     6-10

是否有一种方式来查询这个或存储过程？类似于连接(通过分区)，我对存储过程不太了解。谢谢!

Answer 1

OP是在甲骨文10g上运行的，LISTAGG是在11g发行版2中引入的。

因此，在不支持SYS_CONNECT_BY_PATH的11g之前的Oracle 中，可以使用ROW_NUMBER()和LISTAGG函数。

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版本中删除了。

任何一直依赖wm_concat函数的应用程序一旦升级到12c就无法工作。从那以后，它就被移除了。请参阅Oracle中的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>

您将收到“无效标识符”错误：

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

因此，没有必要依赖在最新版本中不再提供的无文档特性。

Answer 2

你不需要一个存储过程。使用listagg函数：

select fruit, listagg(number, ',') within group (order by number)
from mytable
group by fruit

如果您的数据库版本是10g，它仍然很简单:使用WM_CONCAT (如果不识别名称可能会尝试WMSYS.WM_CONCAT)函数，请参阅这答案中的示例。如果您需要使用与,不同的分隔符，您可以用replace函数包装结果；如果要对结果进行排序，只需在子查询中预先排序，例如：

select fruit, replace(wm_concat(number), ',', '-')
from (select fruit, number
      from mytable
      order by number)
group by fruit

如果您由于某种神秘的原因在实例中没有这个函数，您可以使用一个多边形填充，请参见我的答案这里。