Oracle 拼接列数据的方法

秋羽

　　保存在这里，方便自己以后查看 (⊙_⊙) ╮(╯▽╰)╭
　　
　　我们可以先这样创建一张需要的临时表（Role 表）：

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
　　
　　表结构：


　　
　　现在我们要根据 role_category 分组，把相同 role_category 下的 role_id 连接起来，就是列 role_id 转成行，大家可能很容易想到使用 wm_concat() 方法来进行：

select role_category,
wm_concat(role_id) as role_id_list
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
)
group by role_category
　　
　　好了，我们得到了查询结果：

　　
　　但是这个结果是否有点问题呢？
　　我们发现第2行第2列的数据其 role_id 并不是按照从小到大排序的，似乎并不那么完美，得修改一下sql代码：

-- partition by role_category order by role_id 表示按照 role_category 分组，按照 role_id 排序
select role_category,   
wm_concat(role_id) over(partition by role_category order by role_id)   
as role_id_list   
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   
)
　　
　　现在的查询结果：

　　
　　这次的 role_id 都是排好序的，但是查询结果太多了，我们需要的只是最长的那一行数据，在 sql 中加一个 max() 方法就可以了：

select role_category,
max(role_id_list)
from   (
select role_category,
wm_concat(role_id) over(partition by role_category order by role_id)
as role_id_list
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
)
)
group by role_category
 　　这次我们得到了想要的结果：

　　
　　但是为了得到这两条数据我们查出的数据多了点，而且又用了一次 max() 方法，这在效率上肯定是很低的
　　wm_concat() 是系统函数，也许有的用户登录相关 Oracle 数据库得不到该函数的使用权限（比如我就是）
　　下面再来看一种能够实现同样功能，但是不使用 wm_concat() 函数的方法。
　　
　　同样是使用最开始定义的那张临时表，我们先作一下查询，增加一列 row_number 用来记录 role_category 分组下的 role_id 序号，这里用到了系统函数 row_number()：

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
)
　　
　　查询结果：

　　
　　现在我们根据 row_number 把相同 role_category 的 role_id 用系统函数 sys_connect_by_path() 连接起来：

select role_category,
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
 　　查询结果：

　　
　　开头多余了一个逗号，最后再用系统函数 ltrim() 来去掉这个逗号就OK了：

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