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

是否有可能构造SQL来连接列值 多行吗?< / p >

举例如下:

表一个

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有什么帮助吗?

449869 次浏览
小开
最佳答案

有几种方法取决于你有什么版本-参见关于字符串聚合技术的Oracle文档。一个非常常见的方法是使用LISTAGG:

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

然后加入A来挑选你想要的pids。

注意:开箱即用，LISTAGG仅适用于VARCHAR2列。

小开

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线程，您将找到更多内容，包括性能比较。

小开
LISTAGG分析函数是在Oracle 11g Release 2中引入的，使得聚合字符串非常容易。 如果你正在使用11g Release 2，你应该使用这个函数进行字符串聚合。 有关字符串连接的更多信息，请参考下面的url

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

String串联 . br . br

小开

还有一个XMLAGG函数，它适用于11.2之前的版本。因为WM_CONCAT是Oracle不支持，所以建议不要在生产系统中使用它。

使用XMLAGG你可以做以下事情:

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

它的作用是

  • 将ename列的值(用逗号连接)从employee_names表中放到一个xml元素中(带有标签E)
  • 提取其中的文本
  • 聚合XML(连接它)
  • 将结果列命名为Result
小开

在运行select查询之前，运行以下命令:

SET SERVEROUT ON SIZE 6000

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

试试下面的代码:

 SELECT XMLAGG(XMLELEMENT(E,fieldname||',')).EXTRACT('//text()') "FieldNames"
FROM FIELD_MASTER
WHERE FIELD_ID > 10 AND FIELD_AREA != 'NEBRASKA';
小开
  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; < / p >

  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; < / p >

其他所有技术都要慢一些。

小开

在想要连接的选择中，调用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

Function Header语法可能是错误的，但原理是正确的。

小开

对于那些必须使用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 < / p >
小开

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

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

在12cR2中增加的一个新特性是LISTAGG的ON 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..':将在 字符串的结尾(默认为...)
  • ON OVERFLOW TRUNCATE '':这将显示4000个字符
  • ON OVERFLOW TRUNCATE WITH COUNT:这将显示总数 在结束字符之后的字符数。 如:“...(5512)”李< / >
  • ON OVERFLOW ERROR:如果你期望LISTAGG与 ORA-01489错误(无论如何都是默认值)

提交答案