Oracle中分组后拼接分组字符串

本文介绍了一种在 Oracle 数据库中使用 SQL 实现行转列的方法,通过具体实例展示了如何将多行记录中的某个字段值拼接成单行的多个字段值。此方法涉及到了递归查询、窗口函数等高级 SQL 技术。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

先分组,再把分组后的属于某组的多条记录的某字段进行拼接。

实现方式如下:

/* --创建表 test*/
create table test
(
NO NUMBER,
VALUE VARCHAR2(100),
NAME VARCHAR2(100)
);
/* ----插入数据*/
insert into test
select * from
(
select '1','a','测试1' from dual union all
select '1','b','测试2' from dual union all
select '1','c','测试3' from dual union all
select '1','d','测试4' from dual union all
select '2','e','测试5' from dual union all
select '4','f','测试6' from dual union all
select '4','g','测试7' from dual
);
/*--Sql语句:*/
select No,
ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;

/*--检索结果如下:*/
/*
NO VALUE NAME
1 a;b;c;d 测试1;测试2;测试3;测试4
2 e 测试5
4 f;g 测试6;测试7
*/


/********************************* 分析 *************************************/
--简单解释一下那个Sql吧:
/*--1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:*/
select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a;
/*
该语句结果如下:
NO VALUE NAME RNFIRST
1 d 测试4 1
1 c 测试3 2
1 b 测试2 3
1 a 测试1 4
2 e 测试5 5
4 g 测试7 6
4 f 测试6 7
*/
/*--2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:*/
select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
/*--lead(rnFirst):取得下一行记录的rnFirst字段
--over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,
--分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示*/
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1;

/*
--该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1 d 测试4 1 2
1 c 测试3 2 3
1 b 测试2 3 4
1 a 测试1 4 NULL
2 e 测试5 5 NULL
4 g 测试7 6 7
4 f 测试6 7 NULL
*/


/*--3、最后就是最外层的sys_connect_by_path函数与start递归了*/
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
/*
--这个大概意思就是从rnNext为null的那条记录开始,递归查找,
--如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
--大家可以先试试下面这个没有Max和Group的Sql:
*/
select No,
sys_connect_by_path(Value, ';') as Value,
sys_connect_by_path(Name, ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

/*
结果是:
NO VALUE NAME
1 ;a ;测试1
1 ;a;b ;测试1;测试2
1 ;a;b;c ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e ;测试5
4 ;f ;测试6
4 ;f;g ;测试6;测试7
*/

/*
--可以看到,每个No的最后一条记录就是我们要的了
--所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
--最后再加一个Ltrim,去掉最前面的那个分号,完成。
*/

### Oracle 数据库中的字符串拼接方法 #### 使用 `||` 运算符进行简单字符串拼接Oracle 数据库中,最常用的字符串拼接方式是使用双竖线运算符 (`||`)。这种方式适用于简单的字符串连接操作。 ```sql SELECT 'Hello' || ' ' || 'World' AS greeting FROM dual; ``` 此查询会返回单列结果 `"greeting"` 值为 "Hello World"[^3]。 #### 利用 SQL 函数进行复杂字符串组合 对于更复杂的场景,则可以借助内置函数如 `CONCAT()` 来完成任务;不过需要注意的是,`CONCAT()` 只能接受两个参数作为输入并将其相连,因此当涉及三个及以上部分时需嵌套调用该函数: ```sql SELECT CONCAT(CONCAT('First', 'Second'), 'Third') as combined_string FROM DUAL; ``` 上述命令同样实现了三段文字的串联效果[^4]。 #### 处理大量数据集下的高效解决方案 面对海量记录集合时,为了提高效率减少资源消耗,建议采取更为优化的技术手段比如通过 PL/SQL 编程语言编写存储过程,在其中运用循环结构逐条读取待处理项再实施累加动作直至遍历结束为止。另外还可以探索利用 XMLType 或 JSON 对象来进行批量转换从而达到目的。 #### 高级应用:CLOB 类型的大文本对象拼接 针对超大尺寸字符流(超过 4GB),则应选用 CLOB 数据类型配合特定 API 实现安全可靠的追加写入功能。例如,可通过 DBMS_LOB 包提供的接口执行此类作业: ```plsql DECLARE clob_data CLOB := EMPTY_CLOB(); BEGIN INSERT INTO my_table (id, description) VALUES (1, clob_data); DBMS_LOB.WRITEAPPEND(clob_data, LENGTH('Some large text...'), 'Some large text...'); END; / ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值