是否可以构造 SQL 来连接来自多行?
以下是一个例子:
表A
<前>PID一种乙C表 B
<前>PID 序列描述A 1 有A 2 不错一个3天.B 1 干得漂亮.C 1 是C 2 我们可以C 3 做C 4 这个作品!SQL 的输出应该是 -
<前>PID 描述A 祝你有美好的一天.B 干得好.C 是的,我们可以完成这项工作!所以基本上输出表的 Desc 列是表 B 中 SEQ 值的串联?
对 SQL 有任何帮助吗?
根据您的版本,有几种方法 - 请参阅 关于字符串聚合技术的 oracle 文档.一个很常见的方法是使用 LISTAGG代码>:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS 描述FROM B GROUP BY pid;
然后加入A
来挑选你想要的pids
.
注意:开箱即用,LISTAGG
仅适用于 VARCHAR2
列.
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?
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.
这篇关于用于连接 Oracle 中多行列值的 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!