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 数据库中,`GROUP_CONCAT DISTINCT` 的替代方案可以通过 `LISTAGG` 和其他辅助函数组合实现。以下是具体方法: #### 方法一:使用 `LISTAGG` 结合子查询去重 由于 `LISTAGG` 不支持直接的 `DISTINCT` 关键字[^3],可以先通过子查询对数据进行去重处理后再执行 `LISTAGG`。 ```sql SELECT stu_name, LISTAGG(stu_age, ',') WITHIN GROUP (ORDER BY stu_age) AS stu_age_list FROM ( SELECT DISTINCT stu_name, stu_age FROM student ) GROUP BY stu_name; ``` 上述 SQL 查询首先通过子查询中的 `DISTINCT` 对 `(stu_name, stu_age)` 进行去重操作,然后再利用外部查询调用 `LISTAGG` 函数完成字符串拼接[^2]。 --- #### 方法二:使用 `XMLAGG` 和 `XMLELEMENT` 如果需要更灵活的方式或者目标版本不支持 `LISTAGG`(如某些旧版 Oracle),可以考虑使用 `XMLAGG` 配合 `XMLELEMENT` 来实现类似的字符串拼接效果。 ```sql SELECT stu_name, RTRIM( XMLAGG(XMLELEMENT(E, stu_age || ',')).EXTRACT('//text()').GETCLOBVAL(), ',' ) AS stu_age_list FROM ( SELECT DISTINCT stu_name, stu_age FROM student ) GROUP BY stu_name; ``` 此方法的核心在于: 1. 使用子查询对原始表的数据进行去重; 2. 利用 `XMLAGG` 将每组内的值按顺序拼接为单个字符串; 3. 调整最终结果格式以移除多余的分隔符[^4]。 --- #### 注意事项 - **去重需求**:无论是哪种方式,都需要显式地在内部子查询阶段应用 `DISTINCT` 或者类似逻辑来确保数据唯一性。 - **字段类型限制**:仅适用于字符型字段;对于数值型字段可能需要转换为字符串形式再参与运算。 - **性能考量**:当数据量较大时,应评估不同解决方案之间的效率差异,并优化索引设计以提升查询速度。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值