是否可以构造 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 有帮助吗?
有几种方法取决于您使用的版本 - 请参阅 oracle documentation on string aggregation techniques。一个非常常见的方法是使用 LISTAGG
:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
然后加入 A
以挑选您想要的 pids
。
注意:开箱即用,LISTAGG
仅适用于 VARCHAR2
列。
还有一个 XMLAGG
函数,它适用于 11.2 之前的版本。因为 WM_CONCAT
是 undocumented and unsupported by Oracle,所以建议不要在生产系统中使用它。
使用 XMLAGG
,您可以执行以下操作:
SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result"
FROM employee_names
这是做什么的
将employee_names 表中ename 列的值(用逗号连接)放在一个xml 元素中(带有标签E)
提取此文本
聚合xml(连接它)
调用结果列“结果”
使用 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.
我写过这个here。如果您点击 OTN 线程的链接,您会发现更多内容,包括性能比较。
Oracle 11g 第 2 版中引入了 LISTAGG 分析函数,使得聚合字符串变得非常容易。如果您使用的是 11g 第 2 版,则应使用此函数进行字符串聚合。有关字符串连接的更多信息,请参阅下面的 url。
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php
正如大多数答案所暗示的那样,LISTAGG
是显而易见的选择。但是,LISTAGG
的一个令人讨厌的方面是,如果连接字符串的总长度超过 4000 个字符(SQL 中 VARCHAR2
的限制),则会引发以下错误,这在 Oracle 版本高达 12.1 中很难管理
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..' :这将在字符串末尾显示 'Contd..' (默认为 ...)
ON OVERFLOW TRUNCATE '' :这将显示 4000 个字符而没有任何终止字符串。
ON OVERFLOW TRUNCATE WITH COUNT :这将在终止字符之后显示最后的字符总数。例如:-'...(5512)'
ON OVERFLOW ERROR:如果您希望 LISTAGG 因 ORA-01489 错误而失败(无论如何这是默认设置)。
对于必须使用 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
如果必须进行排序,则 LISTAGG 提供最佳性能(00:00:05.85) SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid;如果不需要排序(00:00:02.90),COLLECT 提供最佳性能:SELECT pid, TO_STRING(CAST(COLLECT(Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;排序的 COLLECT 有点慢(00:00:07.08):SELECT pid, TO_STRING(CAST(COLLECT(Desc ORDER BY Desc) AS varchar2_ntt)) AS Vals FROM B GROUP BY pid;
所有其他技术都较慢。
在运行选择查询之前,运行以下命令:
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';
在选择要连接的位置中,调用 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
函数头语法可能是错误的,但原理确实有效。
不定期副业成功案例分享