时间戳

首页 > 解决方案 > 我们可以同时使用 connect by 子句和 listagg 函数吗

sql - 我们可以同时使用 connect by 子句和 listagg 函数吗

问题描述

我有一列城市值由## 和组名分隔。我想按字母顺序对文本列进行排序

问题：

group   | values
group1  | jammu##bhopal##chandigardh
group2  | Mumbai##kolkatta
group3  | bangalore

预期结果

group   | values
group1  | bhopal##chandigardh##jammu
group2  | kolkatta##Mumbai
group3  | bangalore

我试过下面的代码

select group,listagg(city,'#') within group (order by city asc) as city
from (
      select group, regexp_substr(city,'[^##+',1, LEVEL) as city
     from (
           select group,city from city_group
          )
     connect by regexp_substr(city,'[^##+',1, LEVEL) us not null)
group by group

代码永远运行并且不会产生任何结果。

标签： sqloraclesplit

解决方案

你错过了一点JOIN，所以你的代码真的永远运行。注意第 10 - 12 行；这对您的情况至关重要。

SQL> with test (c_group, c_values) as
  2    (select 'group1', 'jammu##bhopal##chandigardh' from dual union all
  3     select 'group2', 'mumbai##kolkatta'           from dual union all
  4     select 'group3', 'bangalore'                  from dual
  5    ),
  6  temp as
  7    (select c_group,
  8            regexp_substr(c_values, '[^##]+', 1, column_value) col
  9     from test join
 10          table(cast(multiset(select level from dual
 11                              connect by regexp_substr(c_values, '[^##]+', 1, level) is not null
 12                             ) as sys.odcinumberlist)) on 1 = 1
 13    )
 14  select c_group,
 15         listagg(col, '##') within group (order by col) result
 16  from temp
 17  group by c_group
 18  order by c_group;

C_GROU RESULT
------ ----------------------------------------
group1 bhopal##chandigardh##jammu
group2 kolkatta##mumbai
group3 bangalore

SQL>

推荐阅读