Oracle listagg-我可以根据listagg选择的值从其他表中提取数据吗

Question

我有两个表，希望根据从第二个表中的Listtagg获得的值从一个表中获取数据：

T1
ID     Name
==============
1      Name1
2      Person2
3      Someone3
4      Mr.4
T2
ID     Acct
===============
1      1234
1      5678
2      1234
3      5678
3      8769
4      1234

我在T2上的listagg查询返回了以下内容：

Acct   Id
====== ========
1234   1,2,4
5678   1,3

我需要其他表中Names的结果，比如：

Acct   Id       Name
====== ======== ==========
1234   1,2,4    Name1, Person2, Mr.4
5678   1,3      Name1, Someone3

Answer 1

为什么要先聚合IDs，然后努力将它们拆分以收集NAMEs？立即行动。并不是说它不能做到(它可以，以一种相对简单的方式，但是——为什么？！？(。

样本数据来自#1-15行；您可能需要的查询从#16行开始。

SQL> with
2  t1 (id, name) as
3    (select 1, 'Name1' from dual union all
4     select 2, 'Person2' from dual union all
5     select 3, 'Someone3' from dual union all
6     select 4, 'Mr4' from dual
7    ),
8  t2 (id, acct) as
9    (select 1, 1234 from dual union all
10     select 1, 5678 from dual union all
11     select 2, 1234 from dual union all
12     select 3, 5678 from dual union all
13     select 3, 8769 from dual union all
14     select 4, 1234 from dual
15    )
16  select b.acct,
17    listagg(b.id, ', ') within group (order by b.id) id,
18    listagg(a.name, ', ') within group (order by b.id) name
19  from t1 a join t2 b on a.id = b.id
20  group by b.acct;
ACCT ID         NAME
---------- ---------- --------------------
1234 1, 2, 4    Name1, Person2, Mr4
5678 1, 3       Name1, Someone3
8769 3          Someone3
SQL>

Answer 2

@Littlefoot的答案是绝对正确的。但作为补充：如果要拆分这些聚合值，请不要使用listagg。只需使用collect()聚合函数即可获得所需的数据作为集合。例如：

select 
cast(collect(level) as sys.odcinumberlist) as varray_of_numbers,
cast(collect(level) as ORA_MINING_NUMBER_NT) as nested_table_of_numbers,
cast(collect(sys.ku$_objnum(level)) as sys.KU$_OBJNUMSET) as nested_table_of_objnum
from dual connect by level<=3;
--Result:
VARRAY_OF_NUMBERS         NESTED_TABLE_OF_NUMBERS        NESTED_TABLE_OF_OBJNUM(OBJ_NUM)
------------------------- ------------------------------ ------------------------------------------------------------
ODCINUMBERLIST(1, 2, 3)   ORA_MINING_NUMBER_NT(1, 2, 3)  KU$_OBJNUMSET(KU$_OBJNUM(1), KU$_OBJNUM(2), KU$_OBJNUM(3))

更新：这是对您的表的查询，正如您在评论中所问：

select b.acct,
cast(collect(b.id) as ORA_MINING_NUMBER_NT) as nested_table_of_numbers,
cast(collect(a.name) as ORA_MINING_VARCHAR2_NT) as nested_table_of_varchar2
--  listagg(b.id, ', ') within group (order by b.id) id,
--  listagg(a.name, ', ') within group (order by b.id) name
from t1 a join t2 b on a.id = b.id
group by b.acct;

完整示例：

with
t1 (id, name) as
(select 1, 'Name1' from dual union all
select 2, 'Person2' from dual union all
select 3, 'Someone3' from dual union all
select 4, 'Mr4' from dual
),
t2 (id, acct) as
(select 1, 1234 from dual union all
select 1, 5678 from dual union all
select 2, 1234 from dual union all
select 3, 5678 from dual union all
select 3, 8769 from dual union all
select 4, 1234 from dual
)
select b.acct,
cast(collect(b.id) as ORA_MINING_NUMBER_NT) as nested_table_of_numbers,
cast(collect(a.name) as ORA_MINING_VARCHAR2_NT) as nested_table_of_varchar2
--  listagg(b.id, ', ') within group (order by b.id) id,
--  listagg(a.name, ', ') within group (order by b.id) name
from t1 a join t2 b on a.id = b.id
group by b.acct;