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