SQL 查询连接 Oracle 中多行的列值

发布于 2024-10-11 15:04:58 字数 463 浏览 4 评论 0原文

是否可以构造 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(10

星光不落少年眉 2024-10-18 15:04:58

有几种方法,具体取决于您所使用的版本 - 请参阅 有关字符串聚合的 Oracle 文档技术。一种非常常见的方法是使用 LISTAGG

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

然后加入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:

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;

Then join to A to pick out the pids you want.

Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.

君勿笑 2024-10-18 15:04:58

还有一个 XMLAGG 函数,适用于 11.2 之前的版本。由于 WM_CONCAT Oracle 未记录且不支持,因此建议不要使用它在生产系统中。

使用 XMLAGG,您可以执行以下操作:

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result" 
FROM employee_names

它的作用是

  • employee_names 表中的 ename 列的值(用逗号连接)放入在 xml 元素(带有标签 E)中
  • 提取此聚合的文本
  • xml(连接它)
  • 调用结果列“Result”

There's also an XMLAGG function, which works on versions prior to 11.2. Because WM_CONCAT is undocumented and unsupported by Oracle, it's recommended not to use it in production system.

With XMLAGG you can do the following:

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result" 
FROM employee_names

What this does is

  • put the values of the ename column (concatenated with a comma) from the employee_names table in an xml element (with tag E)
  • extract the text of this
  • aggregate the xml (concatenate it)
  • call the resulting column "Result"
把时间冻结 2024-10-18 15:04:58

使用 SQL 模型子句:

SQL> select pid
  2       , ltrim(sentence) sentence
  3    from ( select pid
  4                , seq
  5                , sentence
  6             from b
  7            model
  8                  partition by (pid)
  9                  dimension by (seq)
 10                  measures (descr,cast(null as varchar2(100)) as sentence)
 11                  ( sentence[any] order by seq desc
 12                    = descr[cv()] || ' ' || sentence[cv()+1]
 13                  )
 14         )
 15   where seq = 1
 16  /

P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!

3 rows selected.

我在此处写了有关此内容。如果您点击 OTN 线程的链接,您会发现更多信息,包括性能比较。

With SQL model clause:

SQL> select pid
  2       , ltrim(sentence) sentence
  3    from ( select pid
  4                , seq
  5                , sentence
  6             from b
  7            model
  8                  partition by (pid)
  9                  dimension by (seq)
 10                  measures (descr,cast(null as varchar2(100)) as sentence)
 11                  ( sentence[any] order by seq desc
 12                    = descr[cv()] || ' ' || sentence[cv()+1]
 13                  )
 14         )
 15   where seq = 1
 16  /

P SENTENCE
- ---------------------------------------------------------------------------
A Have a nice day
B Nice Work.
C Yes we can do this work!

3 rows selected.

I wrote about this here. And if you follow the link to the OTN-thread you will find some more, including a performance comparison.

垂暮老矣 2024-10-18 15:04:58

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

岁月流歌 2024-10-18 15:04:58

正如大多数答案所表明的那样,LISTAGG 是显而易见的选择。然而,LISTAGG 的一个烦人的方面是,如果连接字符串的总长度超过 4000 个字符(SQL 中 VARCHAR2 的限制),则会抛出以下错误,这很困难在 Oracle 版本高达 12.1 中进行管理

ORA-01489: 字符串连接结果太长

12cR2 中添加的一个新功能是 LISTAGGON OVERFLOW 子句。
包含此子句的查询如下所示:

SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

上面的命令将输出限制为 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 with LISTAGG is that if the total length of concatenated string exceeds 4000 characters( limit for VARCHAR2 in SQL ), the below error is thrown, which is difficult to manage in Oracle versions upto 12.1

ORA-01489: result of string concatenation is too long

A new feature added in 12cR2 is the ON OVERFLOW clause of LISTAGG.
The query including this clause would look like:

SELECT pid, LISTAGG(Desc, ' ' on overflow truncate) WITHIN GROUP (ORDER BY seq) AS desc
FROM B GROUP BY pid;

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..' at
    the end of string (Default is ... )
  • ON OVERFLOW TRUNCATE '' : This will display the 4000 characters
    without any terminating string.
  • ON OVERFLOW TRUNCATE WITH COUNT : This will display the total
    number of characters at the end after the terminating characters.
    Eg:- '...(5512)'
  • ON OVERFLOW ERROR : If you expect the LISTAGG to fail with the
    ORA-01489 error ( Which is default anyway ).
千柳 2024-10-18 15:04:58

对于那些必须使用 Oracle 9i(或更早版本)解决此问题的人,您可能需要使用 SYS_CONNECT_BY_PATH,因为 LISTAGG 不可用。

为了回答 OP,以下查询将显示表 A 中的 PID 并连接表 B 中的所有 DESC 列:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT a.pid, seq, description
              FROM table_a a, table_b b
              WHERE a.pid = b.pid(+)
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

还可能存在键和值全部包含在一个表中的情况。在不存在表 A、仅存在表 B 的情况下,可以使用以下查询:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT pid, seq, description
              FROM table_b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

所有值都可以根据需要重新排序。各个串联描述可以在 PARTITION BY 子句中重新排序,并且 PID 列表可以在最终的 ORDER BY 子句中重新排序。


或者:有时您可能希望将整个表中的所有值连接到一行中。

这里的关键思想是使用人工值来连接描述组。

在以下查询中,使用常量字符串“1”,但任何值都可以:

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
       FROM (
              SELECT '1' unique_id, b.pid, b.seq, b.description
              FROM table_b b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 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:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT a.pid, seq, description
              FROM table_a a, table_b b
              WHERE a.pid = b.pid(+)
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

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:

SELECT pid, SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY pid ORDER BY pid, seq) rnum, pid, description
       FROM (
              SELECT pid, seq, description
              FROM table_b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR pid = pid
GROUP BY pid
ORDER BY pid;

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:

SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (description, ', ')), 3) all_descriptions
FROM (
       SELECT ROW_NUMBER () OVER (PARTITION BY unique_id ORDER BY pid, seq) rnum, description
       FROM (
              SELECT '1' unique_id, b.pid, b.seq, b.description
              FROM table_b b
             )
      )
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1;

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

红焚 2024-10-18 15:04:58
  1. 如果必须排序,LISTAGG 可提供最佳性能(00:00:05.85)

    SELECT pid, LISTAGG(Desc, ' ')WITHIN GROUP (ORDER BY seq) AS 描述
    FROM B GROUP BY pid;

  2. 如果不需要排序,COLLECT 可以提供最佳性能(00:00:02.90):

    SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

  3. 按顺序收集有点慢(00:00:07.08 ):

    SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;

所有其他技术都较慢。

  1. 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;

  2. 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;

  3. 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.

初雪 2024-10-18 15:04:58

在运行选择查询之前,请运行以下命令:

SET SERVEROUT ON SIZE 6000

SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()') "SUPPLIER" 
FROM SUPPLIERS;

Before you run a select query, run this:

SET SERVEROUT ON SIZE 6000

SELECT XMLAGG(XMLELEMENT(E,SUPLR_SUPLR_ID||',')).EXTRACT('//text()') "SUPPLIER" 
FROM SUPPLIERS;
不爱素颜 2024-10-18 15:04:58

试试这个代码:

 SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
    FROM FIELD_MASTER
    WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';

Try this code:

 SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
    FROM FIELD_MASTER
    WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';
一抹苦笑 2024-10-18 15:04:58

在选择要连接的位置中,调用 SQL 函数。

例如:

select PID, dbo.MyConcat(PID)
   from TableA;

那么对于 SQL 函数:

Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin

declare @x varchar(1000);

select @x = isnull(@x +',', @x, @x +',') + Desc
  from TableB
    where PID = @PID;

return @x;

end

函数头语法可能是错误的,但原理确实有效。

In the select where you want your concatenation, call a SQL function.

For example:

select PID, dbo.MyConcat(PID)
   from TableA;

Then for the SQL function:

Function MyConcat(@PID varchar(10))
returns varchar(1000)
as
begin

declare @x varchar(1000);

select @x = isnull(@x +',', @x, @x +',') + Desc
  from TableB
    where PID = @PID;

return @x;

end

The Function Header syntax might be wrong, but the principle does work.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文