连接Oracle中的数据表

Question

连接Oracle中的数据表

4

这是样例表格数据

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

我尝试将表格列的值连接起来，以获得以下结果。

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

有没有办法查询这个存储过程？类似于按照分区连接，我对存储过程了解不多。谢谢！

- Sailormoon

1

你的Oracle数据库版本是什么？LISTAGG从11g版本开始提供。否则，请在Google上搜索Oracle中的字符串聚合技术。 - Lalit Kumar B

1

请查看此答案 http://stackoverflow.com/a/28690752/3989608 - Lalit Kumar B

1

谢谢，版本是10g，所以我不能真正使用Listagg。还有其他的方法吗？ - Sailormoon

你不能在10g上使用LISTAGG，因为它是在11gR2中引入的。 - Lalit Kumar B

@SailorMoon 添加了 Oracle 10g 的解决方案，请查看更新后的答案。 - smnbbrv

2个回答

1

OP正在使用Oracle 10g，而LISTAGG是在11g Release 2中引入的。

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

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中使用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>

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

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

因此，依赖于未记录的功能是没有意义的，最新版本中也不再提供这个功能。

- Lalit Kumar B

永远不要使用WM_CONCAT - 为什么？如果您查看例如这里（顺便说一下，我的答案中也指出了这一点），您总是可以在12c中将其添加回去。 - smnbbrv

@smnbbrv 或许你并不关心你的数据库和对Oracle支持的需求。当说某个功能“未记录文档”时，这意味着如果出现问题，你就只能自己解决了。那么在生产环境中，你如何处理这些问题呢？或者你根本不在意？ - Lalit Kumar B

当你遇到问题时，首先来到StackOverflow，你在这里不孤单。我的实时系统一直在运行，很有趣的是，Oracle支持从未帮助过我，可能是因为他们只是像你一样炫耀。你相信自己是正确的，但事实上，我的答案被5个人接受，而你的只有一个，这说明可能并非如此。你在这里的行为让我再次考虑是否要去寻求Oracle支持。 - smnbbrv

@smnbbrv 每个使用 Oracle 数据库的行业都购买许可证并支付支持费用。我没有看到任何一个生产系统在没有许可证和支持的情况下运行。而且，当你提到 LISTAGG 时，我可以清楚地看到人们投了你的票，这是很好的，我也通过赞同来表示了感谢。但是，建议使用 wm_concat 是不好的，我刚刚解释了为什么，因为它不是行业标准。想象一下，有人从 10g 升级到 11g，并发现该功能不可用。 - Lalit Kumar B

你可能会争辩说你会编写一个用户定义函数。但是为什么呢？内置函数比用户定义函数更快。您将不得不重写代码以使用新的LISTAGG。有多个字符串聚合选项，其中工作区管理器提供的wm_concat不被Oracle推荐。当然，您可以继续争论在PL/SQL中做事情，您可以通过编程做任何事情。但是，如果您可以在纯SQL中完成它，则无需使用PL/SQL。 - Lalit Kumar B

显示剩余3条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- smnbbrv · Accepted Answer

你不需要存储过程来实现这个功能。使用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

如果您的实例中出现了某些神秘的原因导致该函数不存在，您可以使用一个“polyfill”，请参见我的回答这里。