在 Oracle 12C 中使用 LISTAGG 检索不同的值 [英] Retrieve distinct values with LISTAGG in Oracle 12C

问题描述

我正在尝试使用 oracle 12C 中的 LISTAGG、LISTAGG(distinct...) 解决了 19c 上的问题，但我必须使用 12c.

意外结果

我使用这个查询得到了上面的结果:

<预><代码>选择T.c1 A,T.c2 B,LISTAGG( TI.CODE , ';' ) 组内(按 TI.CODE 排序)作为 COLUMNX1，LISTAGG( TE.CODE, ';') WITHIN GROUP (ORDER BY TE.CODE ) AS COLUMNX2,LISTAGG(TR.CODE, '; ') 组内(按 TR.CODE 排序)作为 COLUMNX3从表 1 开始INNER join TABLE_I TI on TI.fk_c2 = T.c2INNER join TABLE_E TE on TE.fk_c2 = T.c2在 TR.fk_c2 = T.c2 上 INNER 加入 TABLE_R TRWHERE T.d = *参数*通过...分组T.c1,T.c2;

我想找回这个:

预期结果

不应检索黄色标记的字符串.

在查询结果的每一行中，列COLUMNX1、COLUMNX2、COLUMNX3具有相同数量的串联字符串，这就是为什么我有重复问题的原因.

此外，TABLE_I、TABLE_E 和 TABLE_R 都有一个引用 TABLE1.c2 的外键 fk_c2

我先添加了一个 with 子句来检索不同的值，然后我将它加入到我的选择语句中

使用此查询检索预期结果

<预><代码>使用 TEMP AS (SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X1从 (选择不同 *从 TABLE_IGROUP BY fk_c2 ) COLUMNX1内部联接(SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X2从 (选择不同 *来自 TABLE_E)GROUP BY fk_c2 ) COLUMNX2在 COLUMNX1.fk_c2 = COLUMNX2.fk_c2内部联接(SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X3从(选择不同 *来自 TABLE_R)GROUP BY fk_c2 ) COLUMNX3在 COLUMNX1.fk_c2 = COLUMNX3.fk_c2)选择T.c1 A,T.c2 B,tmp.X1，tmp.X2，tmp.X3从表 1 开始在 tmp.fk_c2 = T.c2 上内部加入 temp tmpWHERE T.d = *参数*通过...分组T.c1,tc2tmp.X1，tmp.X2，tmp.X3;

解决方案

您需要额外的步骤:首先找到不同的值，然后聚合它们.例如:

SQL>测试 (id, col) 作为2 (选择 1, 'x' from dual union all3 选择 1, 'x' from dual union all4——5 select 2, 'w' from dual union all6 select 2, 't' from dual union all7 select 2, 'w' from dual union all8--9 选择 3, 'i' from dual10),11 -- 首先找到不同的值...12 温度为13(从测试中选择不同的 id、col)14 -- ... 然后聚合它们15 选择 ID，16 listagg(col, ';') 组内(按 col 排序)结果17 从温度18个按id分组；识别结果---------- ----------1 次2吨；w3我SQL>

I am trying to retrieve multiple concatenated distinct varchars (named CODE in query) from multiple rows on multiple columns using LISTAGG in oracle 12C, LISTAGG(distinct...) solves the problem on 19c but I must work with 12c.

Unexpected result

I get the above result using this query:

   SELECT
          T.c1 A,
          T.c2 B,
          LISTAGG( TI.CODE , ';' ) WITHIN GROUP (ORDER BY  TI.CODE) AS  COLUMNX1,
          LISTAGG( TE.CODE, ' ;') WITHIN GROUP (ORDER BY  TE.CODE ) AS COLUMNX2,    
          LISTAGG(TR.CODE, '; ') WITHIN GROUP (ORDER BY TR.CODE ) AS COLUMNX3
   FROM  TABLE1 T
          INNER join  TABLE_I TI on  TI.fk_c2 = T.c2
          INNER join  TABLE_E TE on TE.fk_c2 = T.c2
          INNER join TABLE_R TR on TR.fk_c2 = T.c2
          WHERE T.d = *parameter*
          GROUP BY
          T.c1,
          T.c2;

I want to retrieve this :

Expected result

The yellow marked strings should not be retrieved.

In evey line of the query result, the columns COLUMNX1, COLUMNX2, COLUMNX3 have the same number of concatenated strings, that's why I have the duplication problem.

furthermore, TABLE_I, TABLE_E and TABLE_R all have a foreign key fk_c2 that references TABLE1.c2

EDIT:

I added a with Clause to retrieve distinct values first then I joined it to my select statement

Expected result is retrieved with this query

WITH TEMP AS (

     SELECT fk_c2, LISTAGG(code, ',')  WITHIN GROUP (ORDER BY code) AS X1 
     FROM (
          SELECT DISTINCT * 
          FROM TABLE_I
          GROUP BY fk_c2 ) COLUMNX1

     INNER JOIN
    (SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X2
      FROM (
           SELECT DISTINCT * 
           FROM TABLE_E)
           GROUP BY fk_c2 ) COLUMNX2
     ON COLUMNX1.fk_c2 = COLUMNX2.fk_c2

     INNER JOIN
    (SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X3
     FROM(
          SELECT DISTINCT * 
          FROM TABLE_R)
          GROUP BY fk_c2 ) COLUMNX3    
     ON COLUMNX1.fk_c2 = COLUMNX3.fk_c2
       )

     SELECT
         T.c1 A,
         T.c2 B,
         tmp.X1,
         tmp.X2,
         tmp.X3
  FROM  TABLE1 T
         INNER join  temp tmp on  tmp.fk_c2 = T.c2
         WHERE T.d = *parameter*
         GROUP BY
         T.c1,
         T.c2
         tmp.X1,
         tmp.X2,
         tmp.X3;

解决方案

You'll need additional step: first find distinct values, then aggregate them. For example:

SQL> with test (id, col) as
  2    (select 1, 'x' from dual union all
  3     select 1, 'x' from dual union all
  4     --
  5     select 2, 'w' from dual union all
  6     select 2, 't' from dual union all
  7     select 2, 'w' from dual union all
  8     --
  9     select 3, 'i' from dual
 10    ),
 11  -- first find distinct values ...
 12  temp as
 13    (select distinct id, col from test)
 14  -- ... then aggregate them
 15  select id,
 16         listagg(col, ';') within group (order by col) result
 17  from temp
 18  group by id;

        ID RESULT
---------- ----------
         1 x
         2 t;w
         3 i

SQL>

这篇关于在 Oracle 12C 中使用 LISTAGG 检索不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！