oracle 分组拼接
2024-09-05 21:06:20
方法一：listagg， 参考链接，从oracle11g后出现的新函数
如果拼接的字符串长度超过4000字节，会报ora-01489错误，ora-01489 字符串连接的结果过长 解决方案。
SELECT
id,count(name) as countname,
LISTAGG(to_char(name), ',') WITHIN GROUP(ORDER BY name) AS pjname
FROM
(
select
distinct B.id as id,
B.name as name
from
B left join C on B.name = C.name
where
C.gender = 'female'
)
group by id
方法二：wmsys.wm_concat，参考链接，返回结果类型会受版本影响，不建议使用
select
grp, wmsys.wm_concat(str)
from
(
select 1 grp, 'a1' str from dual
union
select 1 grp, 'a2' str from dual
union
select 2 grp, 'b1' str from dual
union
select 2 grp, 'b2' str from dual
union
select 2 grp, 'b3' str from dual
) t
group by
grp
方法三（1）：sys_connect_by_path，参考链接
select role_category,
ltrim(sys_connect_by_path(role_id, ','), ',') as role_id_list
from (
select role_category,
role_id,
(row_number() over(partition by role_category order by role_id))
as row_number
from (
select 'role_01' as role_id ,'admin' as role_category from dual
union all
select 'role_02' as role_id ,'admin' as role_category from dual
union all
select 'role_03' as role_id ,'normal' as role_category from dual
union all
select 'role_04' as role_id ,'normal' as role_category from dual
union all
select 'role_05' as role_id ,'normal' as role_category from dual
union all
select 'role_06' as role_id ,'normal' as role_category from dual
union all
select 'role_07' as role_id ,'normal' as role_category from dual
union all
select 'role_08' as role_id ,'normal' as role_category from dual
)
)
where connect_by_isleaf = 1
start with row_number = 1
connect by row_number - 1 = prior row_number
and role_category = prior role_category
方法三（2）：sys_connect_by_path，参考链接
select
deptno,
ltrim(max(sys_connect_by_path(ename, ',')), ',') name
from
(
select
deptno,
ename,
row_number() over(partition by deptno order by ename) ro
from
(
select
deptno,ename
from
emp
where
deptno is not null
) t
) newtab
start with newtab.ro = 1
connect by prior newtab.ro = newtab.ro - 1
group by deptno;
方法三（3）：sys_connect_by_path，参考链接
select
deptno,
ltrim(max(sys_connect_by_path(ename, ',')), ',') name
from
(
select
deptno,
ename,
ro,
lead(ro) over(partition by deptno order by ro) ro_next
from
(
select
deptno, ename,
row_number() over(order by deptno, ename) ro
from
emp
where
deptno is not null
) t
) newtab
start with ro_next is null
connect by ro_next = prior ro
group by deptno;
最新文章
- Spark算上下基线
- 马化腾:办公用QQ休闲用微信[Dream Catchers论坛]
- HTML 调用iscroll.js主要事项
- .NET对象与Windows句柄（二）：句柄分类和.NET句柄泄露的例子
- android fragment getActivity()为空的另一个可能
- java中json包的使用以及字符串,map,list,自定义对象之间的相互转换
- dedecms模版制作活动的折叠菜单
- MongoDB 安装和配置
- json字符串对象内嵌json对象
- switch case 支持的 6 种数据类型！
- [C#.Net]全局钩子实现USB扫码枪无焦点状态下扫入
- python之logging
- php 安装xdebug进行调试（phpstorm）
- 回声状态网络（ESN）基础教程
- python基础（七）——网络编程
- Myeclipse 工具优化 [内存一直增加, jsp,javascript 编辑很卡]
- winform 应用log4net做日志记录到mysql
- Convert Application Model Differences
- 第2课 android机器人的翻页相册
- C# “预先生成事件命令行”和“后期生成事件命令行”