首页 文章

如何在Oracle查询上使用listagg上的条件

提问于
浏览 次
0

我有3个表：table_A，table_B和table_C . Table_A具有主键，并由table_B中的外键引用 . Table_C具有由table_B中的外键引用的主键 . 设计是这样的：

T̲a̲b̲l̲e̲_̲A̲
ID_A
TextData

T̲a̲b̲l̲e̲_̲B̲:
ID_B
ID_A
ID_C

T̲a̲b̲l̲e̲ ̲C̲:
ID_C
TextData

查询：

select a.id_a,
       a.textdata,
       listagg(c.textdata, ', ') within group (order by c.id_c) data_c
from   table_a a
       inner join table_b b on (a.id_a = b.id_a)
       inner join table_c c on (b.id_c = c.id_c)
where c.textdata like :data
group by a.id_a, a.textdata;

我只是从c.textdata的一个表中获取数据，而不是从listagg中获取数据 . 如果我尝试使用此查询：

select a.id_a,
       a.textdata,
       listagg(c.textdata, ', ') within group (order by c.id_c) data_c
from   table_a a
       inner join table_b b on (a.id_a = b.id_a)
       inner join table_c c on (b.id_c = c.id_c)
where listagg(c.textdata, ', ') like :data
group by a.id_a, a.textdata;

我得到一个错误： group function is not allowed here

如何给listagg参数一个条件，因为我需要来自listagg的数据？

oracle join listagg

1 回答

  • 1

    您可以尝试包装查询：

    select *
from (
      select a.id_a,
             a.textdata,
             listagg(c.textdata, ', ') within group (order by c.id_c) data_c
      from   table_a a
             inner join table_b b on (a.id_a = b.id_a)
             inner join table_c c on (b.id_c = c.id_c)
      group by a.id_a, a.textdata
     )
where data_c like :data

    或使用HAVING在LISTAGG建造的油田上应用条件：

    select a.id_a,
       a.textdata,
       listagg(c.textdata, ', ') within group (order by c.id_c) data_c
from   table_a a
       inner join table_b b on (a.id_a = b.id_a)
       inner join table_c c on (b.id_c = c.id_c)
group by a.id_a, a.textdata
having listagg(c.textdata, ', ') within group (order by c.id_c) like :data
    回复于

相关问题