问题描述
我想知道是否有更好的解决方案(例如更快的执行力)来解决以下问题。
I wonder if there is a better (i.e. faster execution) solution to the problem described below.
步骤1)
create table t (k number, v1 number, v2 number);
insert into t values (1,1,1);
insert into t values (1,2,2);
insert into t values (1,2,3);
insert into t values (1,3,3);
insert into t values (1,4,3);
insert into t values (2,7,8);
insert into t values (2,7,9);
步骤2)
我想返回以下数据集
(k, v1_list, v2_list)
1, (1,2,3,4), (1,2,3)
2, (7), (8,9)
步骤3)
我能够通过运行listagg()的多个组合来解决该问题,我想知道是否可以通过其他方式实现。
在此示例中,我处理2个属性,但实际上我必须对数百个列表运行类似的语句。如下所示的查询可能会变得很麻烦。我想知道是否可以在一个查询中完成,可能多次使用listagg()?
I am able to solve it by running the multiple combination of listagg() and I wonder if it can be achieved in another way. In this example I deal with 2 attributes but in reality I have to run the similar statements for hundreds of lists. Querying as shown below may become combersome. I wonder if it can be done in one query, possibly using listagg() several times?
with q1 as (
select distinct k, listagg (v1, ', ') within group (order by k) over (partition by k) v1_list
from t
group by k, v1),
q2 as (
select distinct k, listagg (v2, ', ') within group (order by k) over (partition by k) v2_list
from t
group by k, v2)
--
select distinct t.k, v1_list, v2_list from t
--
join q1 on q1.k = t.k
join q2 on q2.k = t.k
感谢您的建议,
-卢卡斯
推荐答案
您可以尝试未记录的函数 wm_concat
You can try undocumented function wm_concat
select k, wm_concat(distinct(v1)), wm_concat(distinct(v2))
from t
group by k
或使用 listagg
select k, listagg(v1, ', ') within group (order by k), listagg(v2, ', ') within group (order by k)
from (
select distinct k, v1, null v2 from t
union all
select distinct k, null v1, v2 from t
)
group by k
这篇关于Oracle LISTAGG()是否具有多个属性?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!