保存在这里,方便自己以后查看 (⊙_⊙) ╮(╯▽╰)╭
我们可以先这样创建一张需要的临时表(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