SQL 查询连接 Oracle 中多行的列值
是否可以构造 SQL 来连接列值 多行?
以下是一个示例:
表 A
PID A B C
表 B
PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C 1 Yes C 2 we can C 3 do C 4 this work!
SQL 的输出应该是 -
PID Desc A Have a nice day. B Nice Work. C Yes we can do this work!
那么基本上输出表的 Desc 列是表 B 中的 SEQ 值的串联?
有 SQL 方面的帮助吗?
Would it be possible to construct SQL to concatenate column values from
multiple rows?
The following is an example:
Table A
PID A B C
Table B
PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C 1 Yes C 2 we can C 3 do C 4 this work!
Output of the SQL should be -
PID Desc A Have a nice day. B Nice Work. C Yes we can do this work!
So basically the Desc column for out put table is a concatenation of the SEQ values from Table B?
Any help with the SQL?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(10)
有几种方法,具体取决于您所使用的版本 - 请参阅 有关字符串聚合的 Oracle 文档技术。一种非常常见的方法是使用
LISTAGG
:
然后加入
A
来选择你想要的pid
。注意:开箱即用,
LISTAGG
仅适用于VARCHAR2
列。There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use
LISTAGG
:Then join to
A
to pick out thepids
you want.Note: Out of the box,
LISTAGG
only works correctly withVARCHAR2
columns.还有一个
XMLAGG
函数,适用于 11.2 之前的版本。由于WM_CONCAT
Oracle 未记录且不支持,因此建议不要使用它在生产系统中。使用
XMLAGG
,您可以执行以下操作:它的作用是
employee_names
表中的ename
列的值(用逗号连接)放入在 xml 元素(带有标签 E)中There's also an
XMLAGG
function, which works on versions prior to 11.2. BecauseWM_CONCAT
is undocumented and unsupported by Oracle, it's recommended not to use it in production system.With
XMLAGG
you can do the following:What this does is
ename
column (concatenated with a comma) from theemployee_names
table in an xml element (with tag E)使用 SQL 模型子句:
我在此处写了有关此内容。如果您点击 OTN 线程的链接,您会发现更多信息,包括性能比较。
With SQL model clause:
I wrote about this here. And if you follow the link to the OTN-thread you will find some more, including a performance comparison.
LISTAGG 分析函数是在 Oracle 11g 第 2 版 中引入的,使得聚合字符串变得非常容易。
如果您使用的是 11g Release 2,则应该使用此函数进行字符串聚合。
有关字符串连接的更多信息,请参阅下面的网址。
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
字符串连接
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings.
If you are using 11g Release 2 you should use this function for string aggregation.
Please refer below url for more information about string concatenation.
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
String Concatenation
正如大多数答案所表明的那样,
LISTAGG
是显而易见的选择。然而,LISTAGG
的一个烦人的方面是,如果连接字符串的总长度超过 4000 个字符(SQL 中VARCHAR2
的限制),则会抛出以下错误,这很困难在 Oracle 版本高达 12.1 中进行管理12cR2 中添加的一个新功能是
LISTAGG
的ON OVERFLOW
子句。包含此子句的查询如下所示:
上面的命令将输出限制为 4000 个字符,但不会引发
ORA-01489
错误。这些是
ON OVERFLOW
子句的一些附加选项:ON OVERFLOW TRUNCATE 'Contd..'
:这将显示'Contd..'
在字符串结尾(默认为
...
)ON OVERFLOW TRUNCATE ''
:这将显示 4000 个字符没有任何终止字符串。
ON OVERFLOW TRUNCATE WITH COUNT
:这将显示总数终止字符之后末尾的字符数。
例如:- '
...(5512)
'ON OVERFLOW ERROR
:如果您预计LISTAGG
会失败并显示ORA-01489
错误(无论如何都是默认的)。As most of the answers suggest,
LISTAGG
is the obvious option. However, one annoying aspect withLISTAGG
is that if the total length of concatenated string exceeds 4000 characters( limit forVARCHAR2
in SQL ), the below error is thrown, which is difficult to manage in Oracle versions upto 12.1A new feature added in 12cR2 is the
ON OVERFLOW
clause ofLISTAGG
.The query including this clause would look like:
The above will restrict the output to 4000 characters but will not throw the
ORA-01489
error.These are some of the additional options of
ON OVERFLOW
clause:ON OVERFLOW TRUNCATE 'Contd..'
: This will display'Contd..'
atthe end of string (Default is
...
)ON OVERFLOW TRUNCATE ''
: This will display the 4000 characterswithout any terminating string.
ON OVERFLOW TRUNCATE WITH COUNT
: This will display the totalnumber of characters at the end after the terminating characters.
Eg:- '
...(5512)
'ON OVERFLOW ERROR
: If you expect theLISTAGG
to fail with theORA-01489
error ( Which is default anyway ).对于那些必须使用 Oracle 9i(或更早版本)解决此问题的人,您可能需要使用 SYS_CONNECT_BY_PATH,因为 LISTAGG 不可用。
为了回答 OP,以下查询将显示表 A 中的 PID 并连接表 B 中的所有 DESC 列:
还可能存在键和值全部包含在一个表中的情况。在不存在表 A、仅存在表 B 的情况下,可以使用以下查询:
所有值都可以根据需要重新排序。各个串联描述可以在 PARTITION BY 子句中重新排序,并且 PID 列表可以在最终的 ORDER BY 子句中重新排序。
或者:有时您可能希望将整个表中的所有值连接到一行中。
这里的关键思想是使用人工值来连接描述组。
在以下查询中,使用常量字符串“1”,但任何值都可以:
可以在 PARTITION BY 子句中对各个串联描述进行重新排序。
此页面上的其他几个答案也提到了这个非常有用的参考:
https://oracle-base.com/articles/misc/string-aggregation-techniques
For those who must solve this problem using Oracle 9i (or earlier), you will probably need to use SYS_CONNECT_BY_PATH, since LISTAGG is not available.
To answer the OP, the following query will display the PID from Table A and concatenate all the DESC columns from Table B:
There may also be instances where keys and values are all contained in one table. The following query can be used where there is no Table A, and only Table B exists:
All values can be reordered as desired. Individual concatenated descriptions can be reordered in the PARTITION BY clause, and the list of PIDs can be reordered in the final ORDER BY clause.
Alternately: there may be times when you want to concatenate all the values from an entire table into one row.
The key idea here is using an artificial value for the group of descriptions to be concatenated.
In the following query, the constant string '1' is used, but any value will work:
Individual concatenated descriptions can be reordered in the PARTITION BY clause.
Several other answers on this page have also mentioned this extremely helpful reference:
https://oracle-base.com/articles/misc/string-aggregation-techniques
如果必须排序,LISTAGG 可提供最佳性能(00:00:05.85)
SELECT pid, LISTAGG(Desc, ' ')WITHIN GROUP (ORDER BY seq) AS 描述
FROM B GROUP BY pid;
如果不需要排序,COLLECT 可以提供最佳性能(00:00:02.90):
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
按顺序收集有点慢(00:00:07.08 ):
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
所有其他技术都较慢。
LISTAGG delivers the best performance if sorting is a must(00:00:05.85)
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
COLLECT delivers the best performance if sorting is not needed(00:00:02.90):
SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
COLLECT with ordering is bit slower(00:00:07.08):
SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
All other techniques were slower.
在运行选择查询之前,请运行以下命令:
SET SERVEROUT ON SIZE 6000
Before you run a select query, run this:
SET SERVEROUT ON SIZE 6000
试试这个代码:
Try this code:
在选择要连接的位置中,调用 SQL 函数。
例如:
那么对于 SQL 函数:
函数头语法可能是错误的,但原理确实有效。
In the select where you want your concatenation, call a SQL function.
For example:
Then for the SQL function:
The Function Header syntax might be wrong, but the principle does work.